Updating PIVOT table

[scotball]

Hey,

So here's the latest problem...

I have created a dashboard based on a number of pivot tables. (Using GETPIVOTDATA tags)

One of the fields in the Dash tab is Week which is a drop down menu of 1-13. (GLA Dash C3)

I have two pivot tables in seperate tabs. (BottomQuartile1 and BottomQuartile2) both have Week as Report Filter.

At the moment when I change the week in Dash from 4 to 5 i have to manually go into the two BottomQuartile tabs and alter the week to match.

Is there anyway I can automate this process so that when i update the week in Dash it will update the Week in the other two tabs?

Thanks,
Lee

 

[Andrew Poulsom]

See if you can adapt the code I posted here:

http://www.mrexcel.com/forum/showthread.php?t=31090

 

[scotball]

I have found your code but not 100% sure how to adapt it.

Couple of questions then:

1) Where do i paste the code?
2) What are pagefields?

Sorry for the newbie response lol...

Private Sub Worksheet_Calculate()
    Dim PF1 As PivotField
    Dim PF2 As PivotField
    Dim x As String
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Set PF1 = ActiveSheet.PivotTables("PivotTable1").PageFields(1)
    Set PF2 = ActiveSheet.PivotTables("PivotTable2").PageFields(1)
    x = PF1.CurrentPage
    PF2.CurrentPage = x
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub

 

[Andrew Poulsom]

The code goes in the module for the worksheet which contains the pivot table you want to use as the controller. A pivot table has 4 areas - Page, Row, Column and Data. A page field is in the Page area.

 

[scotball]

I have named the first pivot table BQ_Pivot1 (on tab BottomQuartile1) and the 2nd BQ_Pivot2 (on tab BottomQuartile2).

I want them to then update the Week field to be the same as GLA Dash!$C$3

When I add the code i get the following error:

Run-time error '1004':
Unable to get the PivotTables property of the Worksheet class.

When i hit Debug it highlights:

Set PF2 = ActiveSheet.PivotTables("BQ_Pivot2").PageFields(1)

 

[Andrew Poulsom]

Shouldn't it be?

Set PF2 = WorkSheets("BottomQuartile2").PivotTables("BQ_Pivot2").PageFields(1)

 

[scotball]

You are right and now i dont get an error thank you so far And for your patience with a VB noob

Only problem is that it doesnt update the 2nd page with the same week as the first page.

What do i need to add to tell it to take the Week value from GLA Dash!$C$3?

 

[Andrew Poulsom]

Please post your current code.

 

[scotball]

As you can tell... i am pretty rubbish at this lol

Private Sub Worksheet_Calculate()
    Dim PF1 As PivotField
    Dim PF2 As PivotField
    Dim x As String
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Set PF1 = ActiveSheet.PivotTables("BQ_Pivot1").PageFields(1)
    Set PF2 = Worksheets("BottomQuartile2").PivotTables("BQ_Pivot2").PageFields(1)
    x = PF1.CurrentPage
    PF2.CurrentPage = x
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub

 

[Andrew Poulsom]

Try this in the module for the worksheet containing your dropdown:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address <> "$C$3" Then Exit Sub
    Worksheets("BottomQuartile1").PivotTables("BQ_Pivot1").PageFields(1).CurrentPage = Target.Text
    Worksheets("BottomQuartile2").PivotTables("BQ_Pivot2").PageFields(1).CurrentPage = Target.Text
End Sub