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
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