Macro to update Page fields in multiple Pivots

SG Controller

New Member
Joined
Sep 27, 2011
Messages
1
I've created a dashboard in a file that will allow the user to select criteria using validation drop downs.


I then created the below macro to update every pivot within my workbook based upon the criteria. When running the macro I do not receive and error message, but the pivots do not update. What am I doing wrong?

Sub AllWorkbookPivotsPAGERefresh()
Dim pt As PivotTable
Dim ws As Worksheet
Dim SM_Manager As String
Dim Sector_Mgt As String
Dim Region As String
Dim Sales_Rep As String
Dim Sales_Org As String
Dim Sales_Org_CK As String
Dim PL_Manager As String
Dim PL_Nr As String
Dim PL_Report_Cat As String
Dim Sector As String
Dim Plant As String


SM_Manager = SMngr_Input
Sector_Mgt = Sector_Mgt_Input
Region = Region_Input
Sales_Rep = Sales_Rep_Input
Sales_Org = Sales_Org_Input
Sales_Org_CK = Sales_Org_CK_Input
PL_Manager = PL_Mngr_Input
PL_Nr = PL_NR_Input
PL_Report_Cat = PL_REP_Cat_Input
Sector = Sector_Input
Plant = Plant_Input


For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
On Error Resume Next

pt.PivotFields("Sector Manager").CurrentPage = SM_Manager
pt.PivotFields("Sector Mgt").CurrentPage = Sector_Mgt
pt.PivotFields("Region").CurrentPage = Region
pt.PivotFields("Sales Rep").CurrentPage = Sales_Rep
pt.PivotFields("Sales Org").CurrentPage = Sales_Org
pt.PivotFields("Sales Org Country").CurrentPage = Sales_Org_CK
pt.PivotFields("PL Manager").CurrentPage = PL_Manager
pt.PivotFields("PL Nr").CurrentPage = PL_Nr
pt.PivotFields("PL Reporting Category").CurrentPage
=PL_Report_Cat
pt.PivotFields("Sector").CurrentPage = Sector
pt.PivotFields("Plant").CurrentPage = Plant
Next pt
Next ws

End Sub
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Forum statistics

Threads
1,224,599
Messages
6,179,828
Members
452,946
Latest member
JoseDavid

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top