I created a pivot table by recording a macro.
I then separately created a filter by recording another macro & then added at the end of the first macro as shown below.
I get an application defined error on the last line shown.
A separate issue is that if the pivot table underlying data is artificially restricted to 10 rows the pivot table creation is very fast. If it is not restricted the full table is 130000 rows and the macro takes several hours to run.
Why so slow?
I then separately created a filter by recording another macro & then added at the end of the first macro as shown below.
I get an application defined error on the last line shown.
A separate issue is that if the pivot table underlying data is artificially restricted to 10 rows the pivot table creation is very fast. If it is not restricted the full table is 130000 rows and the macro takes several hours to run.
Why so slow?
Code:
Sub CreatePivot()
'
' CreatePivot Macro
'
'
Dim tStart, tEnd As Date
tStart = Now
ActiveWorkbook.Worksheets("Value Report By CTR 120Days").Activate
Dim rEnd As Long
Dim RangeCut1, RangeCut2, RangeCut3, RangeCut4 As Range
rEnd = u.FindLastRow(2, Worksheets("Value Report By CTR 120Days"))
rEnd = 10
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"'Value Report By CTR 120Days'!R1C1:R" & CStr(rEnd) & "C27", Version:=xlPivotTableVersion12). _
CreatePivotTable TableDestination:="'Pivot'!R1C1", TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion12
Sheets("Pivot").Select
Cells(1, 1).Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("IDs")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Tested AG"), "Sum of Tested AG", xlSum
With ActiveSheet.PivotTables("PivotTable1").PivotFields("TF Defined in AG")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("TF Start Date")
.Orientation = xlRowField
.Position = 3
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("TF End Date")
.Orientation = xlRowField
.Position = 4
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Active")
.Orientation = xlRowField
.Position = 5
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Is Boost Ad")
.Orientation = xlRowField
.Position = 6
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"Is Used For TF Start Date")
.Orientation = xlRowField
.Position = 7
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"Is Used For TF End Date")
.Orientation = xlRowField
.Position = 8
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("TF Impressions"), "Sum of TF Impressions", xlSum
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("TF Clicks"), "Sum of TF Clicks", xlSum
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("TF Conversions"), "Sum of TF Conversions", xlSum
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("TF Spend"), "Sum of TF Spend", xlSum
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("TF Avg. Pos"), "Sum of TF Avg. Pos", xlSum
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Tested AG")
.Orientation = xlPageField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("TF Defined in AG")
.Orientation = xlPageField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Active")
.Orientation = xlPageField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Is Boost Ad")
.Orientation = xlPageField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"Is Used For TF Start Date")
.Orientation = xlPageField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"Is Used For TF End Date")
.Orientation = xlPageField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("TF Start Date"), "Count of TF Start Date", xlCount
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("TF End Date"), "Count of TF End Date", xlCount
With ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"Count of TF Start Date")
.Caption = "Min of TF Start Date"
.Function = xlMin
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Count of TF End Date")
.Caption = "Max of TF End Date"
.Function = xlMax
End With
'***************
'* Cut1
'***************
ActiveSheet.PivotTables("PivotTable1").PivotFields("TF Defined in AG"). _
ClearAllFilters
ActiveSheet.PivotTables("PivotTable1").PivotFields("TF Defined in AG"). _
CurrentPage = "TRUE"