1004 Error in Pivot table.

[Jagdev Singh]

Hi Experts

I am facing 1004 error when trying to run the following pivot code.

The code on which I am getting an error is below:

"ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Data!R1C1:R" & LastRow & "C" & LastCol, Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="Pivot!R6C1", TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion14"

I have attached the error screen-shot with the post.

Sub createPivotTable1()
'refer Image DataSource for source data. Note that the complete data table extends upto row number 49.
'refer Image 1 for PivotTable report created after running below code

Dim PvtTbl As PivotTable
Dim wsData As Worksheet
Dim pivotrng As Range
Dim lrow As Long, lCol As Long
Dim wsPvtTbl As Worksheet
Application.ScreenUpdating = False
'Worksheet which contains the source data
Set wsData = Worksheets("Data")
'Worksheet where the new PivotTable will be created
Set wsPvtTbl = Worksheets("Pivot")
'delete all existing Pivot Tables in the worksheet
'in the TableRange1 property, page fields are excluded; to select the entire PivotTable report, including the page fields, use the TableRange2 property.
For Each PvtTbl In wsPvtTbl.PivotTables
If MsgBox("Delete existing PivotTable!", vbYesNo) = vbYes Then
PvtTbl.TableRange2.Clear
End If
Next PvtTbl
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Data!R1C1:R" & LastRow & "C" & LastCol, Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="Pivot!R6C1", TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion14

Set PvtTbl = wsPvtTbl.PivotTables("PivotTable1")

'Default value of ManualUpdate property is False wherein a PivotTable report is recalculated automatically on each change. Turn off automatic updation of Pivot Table during the process of its creation to speed up code.
PvtTbl.ManualUpdate = True

Set pvtFld = PvtTbl.PivotFields("Assigned")
pvtFld.Orientation = xlRowField
pvtFld.Position = 1
'Set pvtFld = PvtTbl.PivotFields("Assigned")
'pvtFld.Orientation = xlColumnField

'set data field - specifically change orientation to a data field and set its function property:
With PvtTbl.PivotFields("Broker Ref 1 (Policy Ref)")
.Orientation = xlDataField
.Function = xlCount
.NumberFormat = "0"
.Position = 1
End With
With PvtTbl.PivotFields("Assigned")
.Orientation = xlDataField
.Function = xlCount
.NumberFormat = "0"
.Position = 1
End With
PvtTbl.PivotFields("Broker Ref 1 (Policy Ref)").AutoSort Order:=xlAscending, Field:="Count of name"
'turn on automatic update / calculation in the Pivot Table
PvtTbl.ManualUpdate = False

End Sub

 

[Jagdev Singh]

Hi Experts,

I investigated the issue further and find the issue in the "SourceData" code of the below code

"
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
LastCol = Cells(1, Columns.Count).End(xlToLeft).Column

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Data!R1C1:R" & LastRow & "C" & LastCol, Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="Pivot!R6C1", TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion14
"
When I try to set it manually I am able to get the result. I want to set it dynamic..Could someone please help with it.

Regards,
Jaggi

 

[Jagdev Singh]

Hi Experts

I am able to fix the dynamic issue with the below code, but now the code is not allowing me to add more than one Row fields in the code. It throws the attached error of 1004

Error code

With ActiveSheet.PivotTables("PivotTable1")
With .PivotFields("Broker Ref 1 (Policy Ref)")
.Orientation = xlRowField
.Position = 1
End With
With .PivotFields("Assigned")
.Orientation = xlRowField
.Position = 2
End With
End With


Sub createPivotTable1()
Dim PvtTbl As PivotTable
Dim wsData As Worksheet
Dim pivotrng As Range
Dim lrow As Long, lCol As Long
Dim wsPvtTbl As Worksheet
Dim pvtFld As PivotField
Application.ScreenUpdating = False
'Worksheet which contains the source data
Set wsData = Worksheets("Data")
'Worksheet where the new PivotTable will be created
Set wsPvtTbl = Worksheets("Pivot")
'delete all existing Pivot Tables in the worksheet
'in the TableRange1 property, page fields are excluded; to select the entire PivotTable report, including the page fields, use the TableRange2 property.
For Each PvtTbl In wsPvtTbl.PivotTables
If MsgBox("Delete existing PivotTable!", vbYesNo) = vbYes Then
PvtTbl.TableRange2.Clear
End If
Next PvtTbl
RowCount = Application.WorksheetFunction.CountA(Range("A:A"))
Set rngData = Range("A1:X" & RowCount)
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rngData, Version:=xlPivotTableVersion12).CreatePivotTable TableDestination:=wsPvtTbl.Range("A3"), TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion12
Sheets("Pivot").Select
Cells(3, 1).Select
'Set pvtFld = PvtTbl.PivotFields("Broker Ref 1 (Policy Ref)")
'pvtFld.Orientation = xlRowField
'With ActiveSheet.PivotTables("PivotTable1").PivotFields("Broker Ref 1 (Policy Ref)")
'.Orientation = xlRowField
'.Position = 1
'End With
'With ActiveSheet.PivotTables("PivotTable1").PivotFields("Assigned")
'.Orientation = xlRowField
'.Position = 2
'End With
With ActiveSheet.PivotTables("PivotTable1")
With .PivotFields("Broker Ref 1 (Policy Ref)")
.Orientation = xlRowField
.Position = 1
End With
With .PivotFields("Assigned")
.Orientation = xlRowField
.Position = 2
End With
End With

End Sub