vba to Change Pivot fields in 3 workbooks-CodePudding

I have written with code to change pivots fields in single workbook. But I have 3 different workbooks with multiple pivots. Now I want these changes in all 3 workbooks. Any help please.

Note = pivot fields and sheet names are same in all pivots (in all 3 workbooks

Tried this code to change pivot fields in one workbook

Sub Account_Names()

Dim a As String Dim ab As String Dim pt As PivotTable

ThisWorkbook.Worksheets("Analysis").Activate

a = Worksheets("Analysis").Cells(1, 10).Value ab = Worksheets("Analysis").Cells(2, 10).Value

For Each pt In ActiveSheet.PivotTables With pt.PivotFields("Root Account") .CurrentPage = a

End With

Next

For Each pt In ActiveSheet.PivotTables With pt.PivotFields("Year") .CurrentPage = ab

End With

Next

End Sub

CodePudding user response：

The following code assumes that the 3 workbooks are already open. Change the name of the workbooks where specified.

Sub Account_Names()

    Dim workbookNames As Variant
    workbookNames = Array("Book1.xlsx", "Book2.xlsx", "Book3.xlsx") 'change the workbook names accordingly
    
    Dim i As Long
    For i = LBound(workbookNames) To UBound(workbookNames)
        
        Dim wb As Workbook
        Set wb = Workbooks(workbookNames(i))
        
        Dim ws As Worksheet
        Set ws = wb.Worksheets("Analysis")
        
        Dim rootAccount As String
        rootAccount = ws.Cells(1, 10).Value
        
        Dim year As String
        year = ws.Cells(2, 10).Value
        
        Dim pt As PivotTable
        For Each pt In ws.PivotTables
            With pt
                With .PivotFields("Root Account")
                    .ClearAllFilters
                    .CurrentPage = rootAccount
                End With
                With .PivotFields("Year")
                    .ClearAllFilters
                    .CurrentPage = year
                End With
            End With
        Next pt
        
    Next i

End Sub