Cell Reference to update multiple pivot tables

[lbanham]

HI, I have the code below which works fine to update a single pivot table (PivotTable3) which is on Worksheet "P&L Analysis". The sheet "P&L Analysis" contains another two pivot tables named "PivotTable10" and "PivotTable11" which I would also like to update automatically.
Can anyone tell me how to amend the code below please?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


If Intersect(Target, Range("B2:B3")) Is Nothing Then Exit Sub


Dim pt As PivotTable
Dim Field As PivotField
Dim NewCat As String
Set pt = Sheet6.PivotTables("PivotTable3")
Set Field = pt.PivotFields("Period")
NewCat = Worksheets("P&L Analysis").Range("B2").Value
With pt
Field.ClearAllFilters
Field.CurrentPage = NewCat
pt.RefreshTable
End With
End Sub


Thanks in advance

 

[jorismoerings]

Hi,

try this code and make sure you change the code to the correct fieldname

<code>Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ws As Worksheet
    Dim pt As PivotTable
    Const strField1 As String = "enter your own field name here!!"
    
    On Error Resume Next
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    
    If Target.Address = "$B$2" Then
        For Each ws In ThisWorkbook.Worksheets
            For Each pt In ws.PivotTables
                With pt.PageFields(strField1)
                .ClearAllFilters
                .CurrentPage = Target.Value
                End With
            Next pt
        Next ws
    End If
   
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub</code>

 

[lbanham]

Thanks for this - the cell reference "B2" is actually on another worksheet called "P&L Analysis", can you advise how to amend the above? It works a great but I would like it to reference to my main tab.
Thanks again

 

[jorismoerings]

Hi,

this code must be placed on the vba code sheet of the worksheet which contains the target cell. As it's placed on the worksheet (in your case P&L analysis) there's no need to refer to the "other" worksheet.

 

[lbanham]

Thanks! works perfectly.