Pivotable Page Field Linking to Another Cell

[walt76]

Does anyone know how to automatically update a page field in pivotable based on another cell? In another words, by changing an input cell info, page field automatically picks up this info. hence changing the pivotable data.

 

[Clarity]

Hi,


As far as I am aware there is no standard way to do this.


It is however possible to change this using VBA using the following line (varies depending on your pivot table):

ActiveSheet.PivotTables("PivotTable1").PivotFields("Prod").CurrentPage = "555"


If you record a macro and change the page field whilst recording you will get the line above for your pivot table. You can then pass a variable (instead of hardcoding the 555)from the sheet to VBA eg:

[pre]Sub Update_Pivot()

Dim PageVal As String

PageVal = Sheets("Sheet1").Range("PageVal").Text

ActiveSheet.PivotTables("PivotTable1").PivotFields("Prod").ClearAllFilters
ActiveSheet.PivotTables("PivotTable1").PivotFields("Prod").CurrentPage = PageVal

End Sub

[/pre]

Where Range PageVal is a named range on Sheet1 of the workbook.


Hope this helps.


Myles

 

[walt76]

Thanks Clarity, it works.