Filter Pivot table based upon list using VBA

[jakeman]

I have a pivot table that I would like to try to filter based upon values from a list on another worksheet, using VBA.

the pivot table is on a worksheet called Sheets("temp_UtilPivot") and the name of the pivot table is temp_UtilData_pivot.

The field in the pivot table is called PCP_Name and the list that I want the PCP_Name field to be filtered off of is on the sheet called Sheets("Lists") and the range of values is stored as a named range = PCP_Selections. PCP_Selections is a dynamic named range that changes in size based upon values added.

I'm using Excel 2010.

thanks.

 

[Andrew Poulsom]

I'm not sure what you mean by "this does not apply to me" but your VBA code looks fine.

 

[EX5TASY]

Sorry, I meant since the dashboard pulls data from the pivot tables I can't switch to manual calculations. Any other ideas?

Why is it that when you select the filter via slicer or pivot table the formulas/data is updated instantaneously versus with VBA it takes much longer?

 

[Andrew Poulsom]

I meant switch calculation to manual at the beginning of your code and back to automatic at the end.

 

[EX5TASY]

Thanks Andrew! I added in the code to switch the calculation before and after and it made the processing much faster.

 

[thompsbs]

Hello! I have the exact same task as jakeman at the beginning of this thread. My code is below; I'm receiving the 'Run Time Error 1004 Application-defined or object-defined error'. Any help would be very much appreciated!

Sub Test()
Dim PI As PivotItem
With Worksheets("Profee").PivotTables("ProfeeCube").PivotFields("UCPG MRN")
.ClearAllFilters
For Each PI In .PivotItems
PI.Visible = WorksheetFunction.CountIf(Range("Cert_MRNs"), PI.Name) > 0
Next PI
End With
End Sub

 

[atreana]

Hello, I have put in the original solution to this and it works. However I would like to do this for multiple pivot tables but with the same list

Below is my code for one pivot table:

Sub Test()
Dim PI As PivotItem
With Worksheets("In Focus - Campaign Analysis").PivotTables("CampaignAnalysis").PivotFields("Campaign Code")
.ClearAllFilters
For Each PI In .PivotItems
PI.Visible = WorksheetFunction.CountIf(Range("CA_Range1"), PI.Name) > 0
Next PI
End With

However I would like to update another pivot table e.g.
Sub MC_Test()
Dim MC As PivotItem
With Worksheets("In Focus - Campaign Analysis").PivotTables("MailCount").PivotFields("MailCount_CC")
.ClearAllFilters
For Each MC In .PivotItems
MC.Visible = WorksheetFunction.CountIf(Range("CA_Range1"), MC.Name) > 0
Next MC
End With
End Sub

However it returns an error when combing the two like below:


Sub Test()
Dim PI As PivotItem
With Worksheets("In Focus - Campaign Analysis").PivotTables("CampaignAnalysis").PivotFields("Campaign Code")
.ClearAllFilters
For Each PI In .PivotItems
PI.Visible = WorksheetFunction.CountIf(Range("CA_Range1"), PI.Name) > 0
Next PI
End With
Dim MC As PivotItem
With Worksheets("In Focus - Campaign Analysis").PivotTables("MailCount").PivotFields("MailCount_CC")
.ClearAllFilters
For Each MC In .PivotItems
MC.Visible = WorksheetFunction.CountIf(Range("CA_Range1"), MC.Name) > 0
Next MC
End With
End Sub

Any help would be great!

 

[atreana]

I managed to figure it out! thanks

Hello, I have put in the original solution to this and it works. However I would like to do this for multiple pivot tables but with the same list

Below is my code for one pivot table:

Sub Test()
Dim PI As PivotItem
With Worksheets("In Focus - Campaign Analysis").PivotTables("CampaignAnalysis").PivotFields("Campaign Code")
.ClearAllFilters
For Each PI In .PivotItems
PI.Visible = WorksheetFunction.CountIf(Range("CA_Range1"), PI.Name) > 0
Next PI
End With

However I would like to update another pivot table e.g.
Sub MC_Test()
Dim MC As PivotItem
With Worksheets("In Focus - Campaign Analysis").PivotTables("MailCount").PivotFields("MailCount_CC")
.ClearAllFilters
For Each MC In .PivotItems
MC.Visible = WorksheetFunction.CountIf(Range("CA_Range1"), MC.Name) > 0
Next MC
End With
End Sub

However it returns an error when combing the two like below:


Sub Test()
Dim PI As PivotItem
With Worksheets("In Focus - Campaign Analysis").PivotTables("CampaignAnalysis").PivotFields("Campaign Code")
.ClearAllFilters
For Each PI In .PivotItems
PI.Visible = WorksheetFunction.CountIf(Range("CA_Range1"), PI.Name) > 0
Next PI
End With
Dim MC As PivotItem
With Worksheets("In Focus - Campaign Analysis").PivotTables("MailCount").PivotFields("MailCount_CC")
.ClearAllFilters
For Each MC In .PivotItems
MC.Visible = WorksheetFunction.CountIf(Range("CA_Range1"), MC.Name) > 0
Next MC
End With
End Sub

Any help would be great!

 

[Coolstudent]

Hi Andrew
I want to filter data based on a list of dates but I want to run the pivot for every value in the list on a button click as for every data the pivot data plots a graph.
What changes do I need to make to your initial code?
I am not a programmer and have not found any help browsing to solve my problem. I don't want to go to the filter and select 300 different values serially to see 300 different graphs. An automatic shift to the next value at a button click is all I am looking for. For every date a 6x9 table is generated.
Would appreciate the help.

 

[dkmanley]

Andrew, I too have been looking for a way to filter a pivot table based on a named range. Your code does indeed work however in my pivot table there are 163,439 unique pivot items, and of course it loops through one by one. Is there any way around this?
The named range "Criteria_select" is a dynamic range and typically won't ever be more than 50 items.

I am updating my lookup tool and in the the original version the macro would identify the pivot item in the source data, select the row, copy to another sheet and repeat. This table was then used as the source for the pivot table. The problem now is, in the new source data there are duplicate pivot items (skus) that need to be included in the pasted source table. The old macro stops when it finds the first one and moves to the next unique number. If I could modify this code to find "all" the skus this would work too.

Here is the original code: How can i modify to lookup all pivot itams (even duplicate) and copy/paste as normal?

Sub create_pivot_table()


Dim Found_Row As Long, loop_cell As Range, paste_row As Long
Application.EnableEvents = False
Application.ScreenUpdating = False


On Error GoTo error_handler
VersionCheck
Reset_Filtered_Data


Sheets("BTO-IM Products list data").Select
'
' Range("PremierDatabase").AdvancedFilter Action:=xlFilterInPlace, _
' CriteriaRange:=Range("Criteria_select")


paste_row = 2
For Each loop_cell In Range("Criteria_select").Offset(0, 1)
If loop_cell <> "NOT" Then


Found_Row = Application.WorksheetFunction.Match(loop_cell, Range("g:g"), 0)
Sheets("BTO-IM Products list data").Range(Cells(Found_Row, 1), Cells(Found_Row, 7)).Copy


Sheets("Filtered Data").Select
Sheets("Filtered Data").Range(Cells(paste_row, 1), Cells(paste_row, 1)).PasteSpecial xlPasteValues
Sheets("BTO-IM Products list data").Select
paste_row = paste_row + 1


End If


Next loop_cell
Application.EnableEvents = True


Sheets("Premier Config - filtered data").Select
ActiveSheet.PivotTables("PivotTable3").PivotCache.refresh


Sheets("Premier Config - filtered data").Select


Exit Sub


MsgBox Err.Description


End Sub

Thanks. dkmanley