Pivot Page field filter based on cell value

[michel.wielens]

Hi, I am new in VBA and I have created a code that gets information
from an access query and set up a pivot table. The worksheet contains
multiple pivottables. All pivot tables have at least one page field
(product). The worksheet should be copied and then every pivot on the
active sheet should be filtered on one product (example: worksheet for
milk, worksheet for cheese, worksheet for etc). This for every product
(couple op 30-35 products).

How can I copy the 'mastersheet' (named ' Master') and filter all
pivot tables for a specific Page field product? May be copy worksheet
and set cell a1 to a specific value?

 

[Hong Quach]

Hi Michel,

You can try the code below.

Dim pt As PivotTable
ActiveSheet.Select
For Each pt In ThisWorkbook.ActiveSheet.PivotTables
pt.PageFields("FIELD_NAME").dataRange = "CHOSEN_VALUE"
Next

You can filter multiple things in this For-Loop. You can also add If Else
statements to check for pt.name to apply extra filter to certain pt in the
sheet.

Note that I didn't have any error checking in here because you didn't ask
for, but as long as you are not asking the macro to filter the pt to
something that is not on the list then you are fine.

Hong Quach

 

[Michel]

Indeed, now I have a problem. The pivottables have two different sources. In
on source, not every value is in. Example: for pt1 I can select "Milk" in the
pagefield "Product", but pt2, doesn't have and returns "All". I would like
that if the error occurs he skips the producing of this pivottable. Is this
possible?

 

[Hong Quach]

Hi Michel,

A quick and simple fix to this problem would probably be adding the "On
Error Resume Next" statement (ignore error) before the loop begin and "On
Error Goto 0" (stop ignoring error).

Other method is to adding an inner loop to check each item in the field.

Hong Quach

 

[Michel]

The error resume next - error goto 0 is not solution I can use. He the
chooses the default filter "All" so every value is shown. What I want him to
do his quite making the specific pivottable. Is this something which is
possible:

So how to program a macro which indicates that if a cell value is not in the
page field list, he should not create the pivot, and if it is in the
pagefield list, he should create it.

 

[Hong Quach]

Hi,

I am not quite understand your situtation described below. My guess is that
you want a macro that will do the following:
1. Set all pt to filter on "All"
2. If all pt contains a "CHOOSEN_VALUE" in the
pt.pagefields("CHOOSEN_FIELD") Then
a. Change all pt to filter on the "CHOOSEN_VALUE"
Else
a. Change all pt back to filter on "All"
3. End If

If what describe here would work for you, then you can make a label at the
bottom of the code to change all pt to filter on "All", then use the "On
Error Goto <Lable>" to reset all pt back to filter on "All".

Private Sub FilterPTOnAll()
' Code to loop through each pt and set the filter to All
End Sub

Sub YourMacro()
FilterPTOnAll
On Error Goto ResetPT

' Code to loop through each pt and set the filter to the "CHOOSEN_VALUE"

Exit Sub
ResetPT:
FilterPTOnAll
End Sub

Hong Quach

 

[Michel]

Thanks will try this!

Hi,

I am not quite understand your situtation described below. My guess is that
you want a macro that will do the following:
1. Set all pt to filter on "All"
2. If all pt contains a "CHOOSEN_VALUE" in the
pt.pagefields("CHOOSEN_FIELD") Then
a. Change all pt to filter on the "CHOOSEN_VALUE"
Else
a. Change all pt back to filter on "All"
3. End If

If what describe here would work for you, then you can make a label at the
bottom of the code to change all pt to filter on "All", then use the "On
Error Goto <Lable>" to reset all pt back to filter on "All".

Private Sub FilterPTOnAll()
' Code to loop through each pt and set the filter to All
End Sub

Sub YourMacro()
FilterPTOnAll
On Error Goto ResetPT

' Code to loop through each pt and set the filter to the "CHOOSEN_VALUE"

Exit Sub
ResetPT:
FilterPTOnAll
End Sub

Hong Quach