One Report Filter for Multiple Pivot Tables (2007)

[susselyd]

I All,

I have 3 Pivot Tables named PT01, PT02 and PT03 in one worksheet based on same table and have same single field in Report Filter.
I want that by changing the Report Filter of PT01, the ones for PT02 and PT03 will update automatically. I've tried the following:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

Dim DateVal As String

DateVal = Target.PivotFields("Month").CurrentPage.Value

PivotTables("PT03").PivotFields("Month").CurrentPage = strPageValue
PivotTables("PT02").PivotFields("Month").CurrentPage = strPageValue

End Sub

Any idea why this does not work and how could I fix this?
Thanks for any help,
D

 

[Domenic]

With the following macro, filter one or more pagefields from any pivottable on the worksheet, and the other pivottables on the same worksheet will update accordingly...

[COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit
[/COLOR]
[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Worksheet_PivotTableUpdate([COLOR=darkblue]ByVal[/COLOR] Target [COLOR=darkblue]As[/COLOR] PivotTable)
    
    [COLOR=darkblue]Dim[/COLOR] ptMain                  [COLOR=darkblue]As[/COLOR] PivotTable
    [COLOR=darkblue]Dim[/COLOR] pfMain                  [COLOR=darkblue]As[/COLOR] PivotField
    [COLOR=darkblue]Dim[/COLOR] piMain                  [COLOR=darkblue]As[/COLOR] PivotItem
    [COLOR=darkblue]Dim[/COLOR] pt                      [COLOR=darkblue]As[/COLOR] PivotTable
    [COLOR=darkblue]Dim[/COLOR] pf                      [COLOR=darkblue]As[/COLOR] PivotField
    [COLOR=darkblue]Dim[/COLOR] bMI                     [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Boolean[/COLOR]
    
    [COLOR=darkblue]With[/COLOR] Application
        .EnableEvents = [COLOR=darkblue]False[/COLOR]
        .ScreenUpdating = [COLOR=darkblue]False[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    
    [COLOR=darkblue]Set[/COLOR] ptMain = Target
    
    [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]Resume[/COLOR] [COLOR=darkblue]Next[/COLOR]
    [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] pfMain [COLOR=darkblue]In[/COLOR] ptMain.PageFields
        bMI = pfMain.EnableMultiplePageItems
        [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] pt [COLOR=darkblue]In[/COLOR] Me.PivotTables
            [COLOR=darkblue]If[/COLOR] pt.Name <> ptMain.Name [COLOR=darkblue]Then[/COLOR]
                pt.ManualUpdate = [COLOR=darkblue]True[/COLOR]
                [COLOR=darkblue]With[/COLOR] pt.PivotFields(pfMain.Name)
                    .ClearAllFilters
                    [COLOR=darkblue]Select[/COLOR] [COLOR=darkblue]Case[/COLOR] bMI
                        [COLOR=darkblue]Case[/COLOR] [COLOR=darkblue]False[/COLOR]
                            .CurrentPage = pfMain.CurrentPage.Value
                        [COLOR=darkblue]Case[/COLOR] [COLOR=darkblue]True[/COLOR]
                            .EnableMultiplePageItems = bMI
                            [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] piMain [COLOR=darkblue]In[/COLOR] pfMain.PivotItems
                                .PivotItems(piMain.Name).Visible = piMain.Visible
                            [COLOR=darkblue]Next[/COLOR] piMain
                    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Select[/COLOR]
                [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
                pt.ManualUpdate = [COLOR=darkblue]False[/COLOR]
            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
        [COLOR=darkblue]Next[/COLOR] pt
    [COLOR=darkblue]Next[/COLOR] pfMain
    [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]GoTo[/COLOR] 0
    
    [COLOR=darkblue]With[/COLOR] Application
        .EnableEvents = [COLOR=darkblue]True[/COLOR]
        .ScreenUpdating = [COLOR=darkblue]True[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Hope this helps!

 

[susselyd]

Hi Domenic, This works perfect, Thank you so so so much ))

 

[Domenic]

That's great, I'm glad I could help! Thanks for the feedback!

Cheers!

 

[fgiovanni]

Hi Domenic,

I am having same problem as Susselyd and have tried your VBA link, however, I can't seem to get it work in my spreadsheet. from same data based I have created four pivot tables, the main idea is that those change when I change the period filter. Any advise?

Thanks in advance mate,

Giovanni