VBA code that runs through pivot tables and refreshes fields/filters

[alex1stef2]

Hi all,

I am currently having a lot of difficulty trying to update legacy code to become dynamic. Effectively I want to create a loop that runs through a bunch of pivot tables, refreshes them according to some some cell value on an index sheet (i.e. new filter is Sheet1!A1 or similar), and then go on to the next one. I will omit the majority of the code as to begin with I just want a few pointers as to how I should be using my variables.

For Each MySheet In ActiveWorkbook.Worksheets


    For Each MyPivot In MySheet.PivotTables

        NewCat = Worksheets("WorkingSheet").Range("B44").Value


        MyPivot.RefreshTable
        Sheets(MySheet).PivotTables(MyPivot).AddDataField
        Sheets(MySheet).PivotTables(MyPivot).PivotFields ("GuidelinePercent"), "GuidelinePercent", xlCount

    With Sheets(MySheet).PivotTables(MyPivot).PivotFields("Date")
        .Orientation = xlRowField
        .Position = 1


    End With

    Sheet(MySheet).PivotTables(MyPivot).PivotFields("GuidelineItemInstanceName").ClearAllFilters
    Sheet(MySheet).PivotTables(MyPivot).PivotFields("GuidelineItemInstanceName").CurrentPage = NewCat



    Next MyPivot
Next MySheet

This is just a skeleton of what my end result will look like (potentially just pseudo-code as it doesn't even come close to fully running). I apologise that my code is awful - I am (obviously!) very new to this.