Automating Pivot Table Filter Selection

[blood_Milk_sky]

Hi,

I have just recorded the below macro to go through and select each account number on a pivot table filter. I was just wondering if anyone knew any code that would do the below for all values in the filter instead of me going through and recording it ac number by account number?

the reason for this is that each time I run this there wont always be the same number of accounts or account numbers in the pivot table filter.

any help would be greatly appreciated.

many thanks

BMS

Sub Macro1()
'
' Macro1 Macro
'

'
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Business" & Chr(10) & "Partner ID"). _
        ClearAllFilters
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Business" & Chr(10) & "Partner ID"). _
        CurrentPage = "120000TST"
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Business" & Chr(10) & "Partner ID"). _
        ClearAllFilters
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Business" & Chr(10) & "Partner ID"). _
        CurrentPage = "120003"
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Business" & Chr(10) & "Partner ID"). _
        ClearAllFilters
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Business" & Chr(10) & "Partner ID"). _
        CurrentPage = "120005"
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Business" & Chr(10) & "Partner ID"). _
        ClearAllFilters
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Business" & Chr(10) & "Partner ID"). _
        CurrentPage = "120008003"
End Sub

 

[sijpie]

Interesting!

 

[blood_Milk_sky]

Hi Sijpie,

did you have any luck with the workbook i sent you? as I have struggled to get it working

 

[sijpie]

I haven't been able to look at it yet. Maybe early next week.