Filter multiple pivot tables 2007

[jtodd]

HI All
I have 4 pivot tables based on the same data set but give different data by week number.
I would like to change the week number in the first pivot ,say 39, and all the other pivots on my sheet would change to 39 as well

I cannot use slicers as the end user only uses 2007

is this possible ? and if so how?

 

[mart37]

You can use a macro.

Put in cell C1 the week number.

Sub UpdatePivottables()
    Dim sh As Worksheet
    Dim pt As PivotTable
    Dim ptField As PivotField
    Dim ptItem As PivotItem
    Dim pc As PivotCache
    For Each sh In ThisWorkbook.Worksheets
        For Each pt In sh.PivotTables
            pt.PivotFields("Weeknumber").ClearAllFilters
            For Each ptItem In pt.PivotFields("Weeknumber").PivotItems
                If ptItem.Name <> ActiveSheet.Range("C1").Value Then
                    ptItem.Visible = False
                End If
            Next ptItem
        Next pt
    Next sh
End Sub