VBA to Select Pivot Table Filter Item

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
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Forum statistics

Threads
1,141,777
Messages
5,708,461
Members
421,571
Latest member
ChaosPup

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
Top