VB Macro to Select/Change Pivot Table Filter

mtskv5

Board Regular
Joined
Jun 17, 2010
Messages
88
This is my first post, so I would first like to say that all the information I have found on this board has been extremely helpful. Thank you.

The issue:

I have a workbook that gets data from a text file, that data then populates a pivot table. The pivot table has one filter critiera (portfolio). One the filter is selected, the data populates an output tab with various formulas, values, extra. That output gets copied and pasted the various tabs within the workbook. Go back to pivot table and select different filter, rinse and repeat for 168 critiera (portfolios)

I currently have macros for all the steps in this process except selecting the pivot table filter critiera. I only need to select the filter critiera one at a time (no multiple values).

I am not a programmer, I have a minimumlistic scope when it comes to vba, however my non-vba excel knowledge is expansive. Thank you for your assistance.

-Matt
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Hi Matt

Have a go with this code...it assumes that there is only one pivot table on the active sheet with only 1 page field (filter).

It will loop through all of the values selecting each PivotItem and doing 'something'.....currently I have left it as a MsgBox to demonstrate that it is working.

The code at the beginning to store the sheet that the Pivot Table is on, and the return to the Pivot sheet after my MsgBox code has run is only required if your other macros jump to other sheets, as tihis code needs to return to the pivot sheet to carry on the loop...from the description of your issue that sounds like what you're doing.

If you need any more help with this or any other code you're using let me know...

Code:
Sub Loop_PivotItems()
'Turn off screen updating
Application.ScreenUpdating = False
'Store the sheet with the Pivot Table
Piv_Sht = ActiveSheet.Name
'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(Piv_Sht).Select
 
Next
'Turn on screen updating
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Thanks Keewhan. Can't wait to give this a shot. I'll let you know if I have any issues.

Thanks again.
 
Upvote 0
Okay, got the code to select (loop) the pivot table filter criteria. Now what I need to do is put a delay in the loop so that I can paste the copied data to another sheet within the book manually. The issue is that a pause or msgbox eliminates the ability to edit the workbook.

Any ideas?
 
Upvote 0
no problem...just remember that this has to be run from the sheet that the Pivot Table is on, or you need to add some code at the beginning of this to select the sheet that it is on first so that it is the active sheet .
 
Upvote 0
sorry didnt see your post just now...could you not add that manual copy process into this code?

if you have some more details on where it is being copied from / to then i can have a look?
 
Upvote 0
I figured out how to select a range through a user input box. What I need to do is paste the clipboard to the user defined range. I've tried the follow:

Code:
 Dim rRange As Range
        Set rRange = Application.InputBox(Prompt:= _
               "Please select where you would like to paste.", _
                   Title:="SPECIFY RANGE", Type:=8)
       If rRange Is Nothing Then
           Exit Sub
       Else
           rRange.Paste
           'figure out the paste function!!!!!!!!!
       End If

The problem I am having is pasting the information to rRange. Any suggestions?
 
Upvote 0
The above code returns a run-time error 1004. Paste method of Range class failed.

:confused:
 
Upvote 0
Okay. Think I am down to just two issues now:

Here is all of the code:
Code:
Sub Loop_PivotItems()
    Sheets("Filter Table").Select
    'to select the sheet with the pivot table
'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....
    Sheets("Output").Select
    Call Output_Copy
    'if statement in order to get paste values for different portfolios
        Dim rRange As Range
        Set rRange = Application.InputBox(Prompt:= _
               "Please select where you would like to paste " + PivotItem, _
                   Title:="Copy and Paste Utility", Type:=8)
       If rRange Is Nothing Then
           GoTo Line1
       Else
       Sheets("Output").Range("A2:G29").Copy _
       Destination:=rRange, Paste:=xlPasteValues
 
       'trying to copy the pasted material and paste specail on top
           'rRange.PasteSpecial (xlPasteAll)
           'figure out the paste function!!!!!!!!!
 
 
       End If
Line1:
            'Return to sheet with the Pivot Table
    Sheets("Filter Table").Select
 
Next
End Sub

This is the first section I am having trouble with:

Code:
 Sheets("Output").Range("A2:G29").Copy _
       Destination:=rRange, Paste:=xlPasteValues

I need to paste the values only. I've tried different ways and running into walls each way I try it.

The next section is that I want to be able to bipass and move to the next iteration in the loop without pasting on some PivotItems. This is what I have tried to do with the GoTo statement with no success:

Code:
If rRange Is Nothing Then
           GoTo Line1
                      'bunch of stuff here      
       End If
Line1:
            'Return to sheet with the Pivot Table
    Sheets("Filter Table").Select

Thanks for any help
 
Last edited:
Upvote 0
Hi...sorry for the delayed reply...

I have a couple of questions for you...

1 - Does the destination range have to be chosen by the user? Is it a fixed sheet / range that it goes to depending on the Pivot Item selected? You could build this logic into the code instead of having a range selection?

2 - Would it be easier to have a list of the Pivot Items at the start of the code which you could select from before the code is executed?
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,163
Members
448,554
Latest member
Gleisner2

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