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
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

mrmmickle1

Well-known Member
Joined
May 11, 2012
Messages
2,456
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.
 

par60056

Well-known Member
Joined
Jul 26, 2012
Messages
1,581
Office Version
  1. 2011
  2. 2010
Platform
  1. Windows
  2. MacOS
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.
 

par60056

Well-known Member
Joined
Jul 26, 2012
Messages
1,581
Office Version
  1. 2011
  2. 2010
Platform
  1. Windows
  2. MacOS
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
 

par60056

Well-known Member
Joined
Jul 26, 2012
Messages
1,581
Office Version
  1. 2011
  2. 2010
Platform
  1. Windows
  2. MacOS
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
 

mrmmickle1

Well-known Member
Joined
May 11, 2012
Messages
2,456
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:

Watch MrExcel Video

Forum statistics

Threads
1,108,924
Messages
5,525,654
Members
409,658
Latest member
Yardcell

This Week's Hot Topics

Top