jasonfuchs70
New Member
- Joined
- Jul 19, 2016
- Messages
- 6
Need some help on the below code. I have a pivot table set up that I want to automate the pivot table filter selection process. I am making this for a clerical person, so I want to make the selection process as easy as possible. the pivot table has the following fields:
Report Filter: Counter (just a number 1 thru 2xx)
Row Labels: Weekending
Facility
Date
Value: Multiple company names
I want the user to be able to push a command button and be prompted to enter the counter number in the Report Filter. The following code, cycles thru all values in the "counter" until it reaches the end of the counter values, then updates the pivot table with the last value in teh counter list. If you could provide input on how to stop the cycle loop, and stop at the entered value and update the pivot table would be great.
Thanks
Sub Select_Filter1XTest()
'Turn off screen updating
Application.ScreenUpdating = False
'Store the sheet with the Pivot Table
Sheets("Sheet1").Select
'Loop through every PivotItem in the PageField (Filter) of the Pivot Table
For Each PivotItem In ActiveSheet.PivotTables(1).PageFields(1).PivotItems
'Select the PivotItem
ActiveSheet.PivotTables(1).PageFields(1).CurrentPage = PivotItem.Value
'Do whatever you need here....
MsgBox (PivotItem.Value)
'Return to sheet with the Pivot Table
Sheets("Sheet1").Select
Next
'Turn on screen updating
Application.ScreenUpdating = True
End Sub
Report Filter: Counter (just a number 1 thru 2xx)
Row Labels: Weekending
Facility
Date
Value: Multiple company names
I want the user to be able to push a command button and be prompted to enter the counter number in the Report Filter. The following code, cycles thru all values in the "counter" until it reaches the end of the counter values, then updates the pivot table with the last value in teh counter list. If you could provide input on how to stop the cycle loop, and stop at the entered value and update the pivot table would be great.
Thanks
Sub Select_Filter1XTest()
'Turn off screen updating
Application.ScreenUpdating = False
'Store the sheet with the Pivot Table
Sheets("Sheet1").Select
'Loop through every PivotItem in the PageField (Filter) of the Pivot Table
For Each PivotItem In ActiveSheet.PivotTables(1).PageFields(1).PivotItems
'Select the PivotItem
ActiveSheet.PivotTables(1).PageFields(1).CurrentPage = PivotItem.Value
'Do whatever you need here....
MsgBox (PivotItem.Value)
'Return to sheet with the Pivot Table
Sheets("Sheet1").Select
Next
'Turn on screen updating
Application.ScreenUpdating = True
End Sub