Pivot Tables - Linking

[JustinGM]

I have a number of Pivot Charts that each have their own Pivot table but I need the user to select the page required from one pivot table and this selection to be used for the rest of the Pivot tables. I know that I can use VBA to specify the selection for any individual pivot table but how would I call this code or is there a better way.
?

Thanks

 

[mrhartley]

Hi, This is not exactly what you have asked for but this is how I have managed something very similar in the past.

I have a need for someone to enter into a cell the number of a month and also the year.

So far this purpose we are using cell A1 and month 11 for November and cell A2 and 2005 for the year.

I then want all my pivot tables month and year page fields to now change to month 11 and 2005.

I have a button on the worksheet that the user can press and this goes and finds all the pivot tables in all the worksheets (that I have specified and updates the month field to 11 and the year to 2005, as follows:

Dim monthstring As String
Dim yearstring As String
Dim PT As PivotTable
Dim I As Integer
Dim sheetlist As Variant

monthstring = Sheets("sheet1").Range("A1").Value
yearstring = Sheets("sheet1").Range("A2").Value

On Error Resume Next
sheetlist = Array("sheet2", "sheet3", "sheet4")
For I = LBound(sheetlist) To UBound(sheetlist)
  For Each PT In Sheets(sheetlist(I)).PivotTables
   PT.PivotFields("Month").CurrentPage = monthstring
   PT.PivotFields("Year").CurrentPage = yearstring
  Next PT
Next I
On Error GoTo 0
    
    Sheets("Menu").Select
    Range("A1").Select
    MsgBox "Pivot table refresh is complete"
End Sub

You can adjust each peice of code for your needs along with sheet names and cell references. I am sure there are other ways to achieve what you want but this is just one suggestion.

Regards
Mark