Can't filter Pivot Table Programmatically

Steve1944

New Member
Joined
Jul 5, 2012
Messages
9
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?



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"
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Forum statistics

Threads
1,214,872
Messages
6,122,025
Members
449,060
Latest member
LinusJE

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top