Pivot Table Pages

[MartinL]

I have 8 pivot tables with 5 pages (dropdowns) on a worksheet called Pivots

I want all 8 pivot tables to display the same thing without manually going through and changing each one. The differences between the tables are the values shown.

There is a link here to the sort of thing I am tryin gto do based on 2 pivot tables and 1 page http://www.mrexcel.com/forum/showthread.php?t=31090

I want my pages to be set according to cells on another worksheet called Selection

This is my code so far:
can anyone see why the link to Selection doesn't work

Private Sub Worksheet_Calculate()
'Based on http://www.mrexcel.com/forum/showthread.php?t=31090
    Dim PF1 As PivotField
    Dim PF2 As PivotField
    Dim PF3 As PivotField
    Dim PF4 As PivotField
    Dim PF5 As PivotField
    Dim PF6 As PivotField
    Dim PF7 As PivotField
    Dim PF8 As PivotField
    Dim PF9 As PivotField
    Dim PF10 As PivotField
    Dim PF11 As PivotField
    Dim PF12 As PivotField
    Dim PF13 As PivotField
    Dim PF14 As PivotField
    Dim PF15 As PivotField
    Dim PF16 As PivotField
    Dim PF17 As PivotField
    Dim PF18 As PivotField
    Dim PF19 As PivotField
    Dim PF20 As PivotField
    Dim PF21 As PivotField
    Dim PF22 As PivotField
    Dim PF23 As PivotField
    Dim PF24 As PivotField
    Dim PF25 As PivotField
    Dim PF26 As PivotField
    Dim PF27 As PivotField
    Dim PF28 As PivotField
    Dim PF29 As PivotField
    Dim PF30 As PivotField
    Dim PF31 As PivotField
    Dim PF32 As PivotField
    Dim PF33 As PivotField
    Dim PF34 As PivotField
    Dim PF35 As PivotField
    Dim PF36 As PivotField
    Dim PF37 As PivotField
    Dim PF38 As PivotField
    Dim PF39 As PivotField
    Dim PF40 As PivotField
    Dim SECT As Range          'PageField2
    Dim SSEC As String          'PageField1
    Dim TIER As String          'PageField5
    Dim GRP As String           'PageField3
    Dim TPE As String           'PageField4
    'Application.EnableEvents = False
    'Application.ScreenUpdating = False
    Set PF1 = ActiveSheet.PivotTables("PivotTable1").PageFields(1)
    Set PF2 = ActiveSheet.PivotTables("PivotTable2").PageFields(1)
    Set PF3 = ActiveSheet.PivotTables("PivotTable3").PageFields(1)
    Set PF4 = ActiveSheet.PivotTables("PivotTable4").PageFields(1)
    Set PF5 = ActiveSheet.PivotTables("PivotTable5").PageFields(1)
    Set PF6 = ActiveSheet.PivotTables("PivotTable6").PageFields(1)
    Set PF7 = ActiveSheet.PivotTables("PivotTable7").PageFields(1)
    Set PF8 = ActiveSheet.PivotTables("PivotTable8").PageFields(1)
    Set PF9 = ActiveSheet.PivotTables("PivotTable1").PageFields(2)
    Set PF10 = ActiveSheet.PivotTables("PivotTable2").PageFields(2)
    Set PF11 = ActiveSheet.PivotTables("PivotTable3").PageFields(2)
    Set PF12 = ActiveSheet.PivotTables("PivotTable4").PageFields(2)
    Set PF13 = ActiveSheet.PivotTables("PivotTable5").PageFields(2)
    Set PF14 = ActiveSheet.PivotTables("PivotTable6").PageFields(2)
    Set PF15 = ActiveSheet.PivotTables("PivotTable7").PageFields(2)
    Set PF16 = ActiveSheet.PivotTables("PivotTable8").PageFields(2)
    Set PF17 = ActiveSheet.PivotTables("PivotTable1").PageFields(3)
    Set PF18 = ActiveSheet.PivotTables("PivotTable2").PageFields(3)
    Set PF19 = ActiveSheet.PivotTables("PivotTable3").PageFields(3)
    Set PF20 = ActiveSheet.PivotTables("PivotTable4").PageFields(3)
    Set PF21 = ActiveSheet.PivotTables("PivotTable5").PageFields(3)
    Set PF22 = ActiveSheet.PivotTables("PivotTable6").PageFields(3)
    Set PF23 = ActiveSheet.PivotTables("PivotTable7").PageFields(3)
    Set PF24 = ActiveSheet.PivotTables("PivotTable8").PageFields(3)
    Set PF25 = ActiveSheet.PivotTables("PivotTable1").PageFields(4)
    Set PF26 = ActiveSheet.PivotTables("PivotTable2").PageFields(4)
    Set PF27 = ActiveSheet.PivotTables("PivotTable3").PageFields(4)
    Set PF28 = ActiveSheet.PivotTables("PivotTable4").PageFields(4)
    Set PF29 = ActiveSheet.PivotTables("PivotTable5").PageFields(4)
    Set PF30 = ActiveSheet.PivotTables("PivotTable6").PageFields(4)
    Set PF31 = ActiveSheet.PivotTables("PivotTable7").PageFields(4)
    Set PF32 = ActiveSheet.PivotTables("PivotTable8").PageFields(4)
    Set PF33 = ActiveSheet.PivotTables("PivotTable1").PageFields(5)
    Set PF34 = ActiveSheet.PivotTables("PivotTable2").PageFields(5)
    Set PF35 = ActiveSheet.PivotTables("PivotTable3").PageFields(5)
    Set PF36 = ActiveSheet.PivotTables("PivotTable4").PageFields(5)
    Set PF37 = ActiveSheet.PivotTables("PivotTable5").PageFields(5)
    Set PF38 = ActiveSheet.PivotTables("PivotTable6").PageFields(5)
    Set PF39 = ActiveSheet.PivotTables("PivotTable7").PageFields(5)
    Set PF40 = ActiveSheet.PivotTables("PivotTable8").PageFields(5)
 
'this bit doesn't work
'=====================
With Worksheets("selection")
   SECT = Range("B4")         'PageField2
   SSEC = Range("D3")         'PageField1
   TIER = Range("F3")         'PageField5
   GRP = Range("H3")          'PageField3
   TPE = Range("J3")          'PageField4
End With
 
    'still working on this bit
   '==========================
 
    'x = PF1.CurrentPage
    'PF2.CurrentPage = x
    'PF3.CurrentPage = x
    'PF4.CurrentPage = x
    'PF5.CurrentPage = x
 
    'Application.ScreenUpdating = True
    'Application.EnableEvents = True
End Sub

 

[MartinL]

hahahaha

ok the following seems to work:

    SECT = Worksheets("Selection").Range("B3")        'PageField2
    SSEC = Worksheets("Selection").Range("D3")         'PageField1
    TIER = Worksheets("Selection").Range("F3")         'PageField5
    GRP = Worksheets("Selection").Range("H3")          'PageField3
    TPE = Worksheets("Selection").Range("J3")          'PageField4