Updating Pivot Table Page Fields based on another page field

[apank]

I have been working with VBA for less than a year, but mostly in Access. I have come accross an issue in Excel that I want to solve programatically.

I have several pivot tables on one work sheet that feed several pivot charts.

I am looking for code that will update each pivot table based on the page I select from the first pivot table on that worksheet.

Basically, once I update the first pivot table, I need each of the remaining pivots to get the same update.

If I can get this to work, it will save me a ton of time.

I appreciate any help you give me.

A

[NeoPa]

I've not worked with these objects before but I've done some VBA. If you'd like to post your working spreadsheet I'll see what I can do for you.

[apank]

Thanks NeoPa. But I found some code on line. I forgot the link, but here is the code that worked for me.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim pt As PivotTable
 
    If Not Intersect(Target(1, 1), Range("Branchcode")) Is Nothing Then
        Set pt = Sheet9.PivotTables(1)
        pt.RefreshTable
    With pt
        .RefreshTable
        .PivotFields("Office Code").CurrentPage = Range("Branchcode").Text
 
        End With
 
    End If
 
End Sub

I simply changed the pivot table # to have it update 9 different pivot tables and subsequent charts.

Thank you for stepping up to help though.

[NeoPa]

Ah. Well done.

With a little tweaking (& tidying) I think we can handle the 9 pivot tables (assuming they're in the one worksheet - Sheet9 from your code). Try this :

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim intX As Integer
 
    If Not Intersect(Target(1, 1), Range("Branchcode")) Is Nothing Then
        For intX = 1 To 9
            With Sheet9.PivotTables(intX)
                Call .RefreshTable
                .PivotFields("Office Code").CurrentPage = Range("Branchcode").Text
            End With
        Next intX
    End If
End Sub