Macro to Filter Pivot Table

currybai

Board Regular
Joined
Jul 14, 2008
Messages
202
Hi,

I have a pivot table that I work on everyday. I pull in a few different fields.

One of the fields that I use is called Campaign Name. Each day I have to manually choose from 20 campaigns that I want to see, out of a list of over 1000.

Is there a way where I have the campaign name on another sheet and have a macro that detects the names on that range and filter the pivot table for me?

Thanks
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Take a look at this example. Maybe it can help you to get some bearings:

Filter Pivot Table Based On WildCard Values

Also it might help to be more specific on how your data is set up in your spreadsheet. This can help for Forum Posters to better understand your specific issue.
 
Upvote 0
Is that supposed to be a link to something? It does not work for me.

Take a look at this example. Maybe it can help you to get some bearings:

Filter Pivot Table Based On WildCard Values

Also it might help to be more specific on how your data is set up in your spreadsheet. This can help for Forum Posters to better understand your specific issue.
 
Upvote 0
Is there a way where I have the campaign name on another sheet and have a macro that detects the names on that range and filter the pivot table for me?

Here is a quick bit of code I hacked together. It is obviously untested but should give you a start.

Assumptions:
1) sheet named "campaigns" contains a list of the campaign names in column A
2) Pivot table is the 1st pivot table on the active sheet. I usually work with named pivot tables but I have no idea what your table is named.

Code:
Sub showCampaigns()


Dim lastRow As Long
Dim currRow As Long
Dim currItem As Integer


lastRow = Sheets("campaigns").Range("A1").End(xlDown).Row


With ActiveSheet.PivotTables(1)
    'turn off the updating because this is going to recalculate the pivot table a lot of times if you don't
    .ManualUpdate = True
    'set the last item visible just to make sure you don't get an error for having all items unchecked
    .PivotFields("Campaign Name").PivotItems(.PivotFields("Campaign Name").PivotItems.Count).Visible = True
    
    ' now set every item to off except the first one on the list
    For currItem = 1 To .PivotFields("Campaign Name").PivotItems.Count
        If (.PivotFields("Campaign Name").PivotItems(currItem).Name = Sheets("campaigns").Range("A1").Value) Then
            .PivotFields("Campaign Name").PivotItems(currItem).Visible = True
        Else
            .PivotFields("Campaign Name").PivotItems(currItem).Visible = True
        End If
    Next
    
    'now turn on all the other campaigns you want to see
    For currRow = 2 To lastRow
        .PivotFields("Campaign Name").PivotItems(Sheets("campaigns").Cells(currRow, 1)).Visible = True
    Next
    
    'turn updating back on to see the magic
    .ManualUpdate = False
End With
End Sub


Hope this helps you find your solution
 
Upvote 0
I don't have a registration there so I can't see the code. I may look at registering to look at it.

Thanks for fixing the link
 
Upvote 0
This is the information from the thread:

Question:

Add Columns to Pivot Table with a name like "ABC"
I have a pivot table that I create weekly to include the new week's data, as well as the rest of the weeks in the year. I'd like for the macro to pull all of the columns like "WE" into the pivot table so that it includes the current week. Below is what the code looks like now. Is there a way to tell the macro to pull columns based on "field name like" language? Thanks in advance for any help you can give me! I've been able to get this far, but it doesn't loop the headers to find all of the columns that have WE headers.

Solution:

Code:
<code style="margin: 0px; padding: 0px; font-style: inherit;">[COLOR=#0000FF]Sub[/COLOR] AddPTFields()

[COLOR=#0000FF]    Dim[/COLOR] PT         [COLOR=#0000FF] As[/COLOR] PivotTable[COLOR=#0000FF] 
    Dim[/COLOR] LastCol     [COLOR=#0000FF]As[/COLOR] [COLOR=#0000FF]Integer[/COLOR][COLOR=#0000FF] 
    Dim[/COLOR] wsName      [COLOR=#0000FF]As[/COLOR] [COLOR=#0000FF]String[/COLOR] 
[COLOR=#0000FF]    Dim[/COLOR] x           [COLOR=#0000FF]As[/COLOR] [COLOR=#0000FF]Long[/COLOR] 

    wsName = "Total Daily Volume Data" 
[COLOR=#0000FF]    Set[/COLOR] PT = ActiveSheet.PivotTables("PivotTable1") 
    LastCol = Sheets(wsName).Range("A1").SpecialCells(xlCellTypeLastCell).Column[COLOR=#0000FF] 

    For[/COLOR] x = 7 [COLOR=#0000FF]To[/COLOR] LastCol 
[COLOR=#0000FF]        If [/COLOR]Sheets(wsName).Cells(1, x + 1).Value [COLOR=#0000FF]Like[/COLOR] "WE*" [COLOR=#0000FF]Then[/COLOR] 
            PT.AddDataField PT.PivotFields(Sheets(wsName).Cells(1, x + 1).Value), _            
            "Sum of " & Sheets(wsName).Cells(1, x + 1).Value, xlSum
[COLOR=#0000FF]        End If[/COLOR] 
[COLOR=#0000FF]    Next[/COLOR] x 
    [COLOR=#0000FF]
End Sub[/COLOR]</code>
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,150
Members
448,552
Latest member
WORKINGWITHNOLEADER

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