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

 

[scotball]

I pasted the code into the sheet and changed the value in C3 to test.

Run-time error '1004':
Application-defined or object-defined error

Worksheets("BottomQuartile1").PivotTables("BQ_Pivot1").PageFields(1).CurrentPage = Target.Text

Any ideas?

 

[scotball]

Should i be referencing which field in the Pivot i want to update?

If so, how?

Thanks again for all your help...

LP

 

[Andrew Poulsom]

I tested the code before I posted it. What do you have in your dropdown and is it the same as the list in the pivot table's page field?

 

[scotball]

It's the same as far as i can see.

The week in the pivot is pulled from a rawdata sheet and the week in the dropdown is simply a list of numbers, 1-13 which would align to the pivot.

Thanks,
Lee

 

[Andrew Poulsom]

What's the number format of your dropdown cell? Is it the same as the cells on the rawdata sheet?

 

[scotball]

yes it is the same format. :S

 

[Andrew Poulsom]

Can you record a macro while changing the page field and post the code?

 

[scotball]

Sub change_pagefield()
'
' change_pagefield Macro
'
'
    ActiveSheet.PivotTables("BQ_Pivot1").PivotFields("Week").ClearAllFilters
    ActiveSheet.PivotTables("BQ_Pivot1").PivotFields("Week").CurrentPage = "2"
End Sub

 

[scotball]

Updated the code in the GLA Dash sheet to see if that was what was wrong (just guessing obviously):

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

And then ensured all of the week formatting is set to General.

I set the week in BQ1 to 2 and in BQ2 to 1. Then I changed the GLA Dash week from 4 to 5 and nothing happened... :S

 

[Andrew Poulsom]

That should work. With the code below do you get a message when you change the week?

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