Modify all pivot tables in one sheet

[Augusto_Madrid]

How can I modify all my pivots at once?

I have some 200 PT and I need them to switch from one filtering variable to
another, ie. if I am filtering by "year" and "type of equipment" (as general
filters, not as rows or columns), and I would need "type of equipment"
replaced by "Seller" in all my Pivot Tables

Can this be done?

Thanks in advance!

 

[Roger Govier]

Hi

Take a look at the sample workbook created by Debra Dalgleish
http://www.contextures.com/PivotMultiPagesChangeAll.zip

 

[Augusto_Madrid]

Hello, Roger, and many thanks

I already did check these files by Debra Dalgleish. And they're awsome. The
thing is I can't seem to find quite what I need

I need a macro to change all the filters in all my PTs at once. Example


YEAR 2007
TYPE OF EQUIPMENT Microwave

Yes 120
No 77
Only partially 99

I need the macro to do this:

YEAR 2007
SELLER Michael

Yes 101
No 33
Only partially 31


I'm not sure if I'm making myself clear, I'm not experienced with macros or
with VBA...

Thanka again!!!!

 

[Patrick Molloy]

Debra's example looks to be exactly what you need
The VBA checks the name of each PAGE field of the main pivot and then, if
those fields are in other pivot table page fields, then they'll be set to
the same value

however, because they use the pivot change event, they fire each time a
change is made. You'll need to copy the code to a standard module, making it
a parameter-less sub, and call it once all changes to the "main" pivot have
been made. Unless therepivots are very complex, i can't see much benefit
to doing it this way

 

[Augusto_Madrid]

Following with this example from Debra, what I need is all the tables to be
filtered by Unit cost if I change Item by Unit cost in the Main Pivot Table.

What I see here is that I can choose Quebec or Ontario, and all PTs change
to Quebec or Ontario...

Am I too wrong?
You are being so patient and helpful, thanks SOOO much

 

[Patrick Molloy]

ah. So you need to change the field itself? yes, that too can be done.
you just need to amend DD's code to read read the page field names and
update the other pivots

 

[Augusto_Madrid]

Ok, I finally got myself understood...I think you're the first one to catch
my point, thanks!!

The thing is, would you be so kind to provide me with the code to do this
you're mentioning. I have no clue about how to do this, and I need it badly
for my work (this will save me many hours of work!!!)

Again, thank you so much for your patience and time,
Augusto

 

[Patrick Molloy]

add this line:

SetPivotFields

immediately after
Application.EnableEvents = False
in the pivottableupdate event for the main sheet

in a new module add the code for SetPivotFields
Basically, we'll simply remove the page fields from the other pivots, then
set them again as per the main pivot

Option Explicit
Sub SetPivotFields()
Dim ptMain As PivotTable
Dim ws As Worksheet
Dim wsMain As Worksheet
Dim pt As PivotTable
Dim pf As PivotField
Dim pfMain As PivotField

Set wsMain = Worksheets("Sales Pivot")
Set ptMain = wsMain.PivotTables(1)

For Each ws In ThisWorkbook.Worksheets
If ws.Name <> wsMain.Name Then
For Each pt In ws.PivotTables
For Each pf In pt.PageFields
pf.Orientation = xlHidden
Next
For Each pf In ptMain.PageFields
pt.PivotFields(pf.Name).Orientation = xlPageField
Next
Next
End If
Next
End Sub

 

[Augusto_Madrid]

Sooo many thanks, Patrick,

I'm almost where I wanted to be!! It's exciting for me, this is my first
time with VBA...

The thing is this code

For Each pf In pt.PageFields
pf.Orientation = xlHidden

basically hides all existing PageFields. And I have some of my PTs where I
would like to keep some of the PageFields, and only change the PageField I
want to change

Any idea?

Thanks again and best from Madrid

 

[Patrick Molloy]

each pivot table is individually named...by default PivotTable1, PivotTable2
and so on, but they can be specifically named as well

the code is pretty general, in that it looks for all pivot tables. there's
no need to do it that way. You can specify each table and make whatever
changes you need to for each table.

set pt = worksheets("sheet2").PivotTables("MyPivot")
with pt
{do something}
end with
set pt = worksheets("sheet3").PivotTables("MyOtherPivot")
with pt
{do something}
end with

 

[Augusto_Madrid]

Once again, thank you so much

each pivot table is individually named...by default PivotTable1, PivotTable2
and so on, but they can be specifically named as well

the code is pretty general, in that it looks for all pivot tables. there's
no need to do it that way. You can specify each table and make whatever
changes you need to for each table.

set pt = worksheets("sheet2").PivotTables("MyPivot")
with pt
{do something}
end with
set pt = worksheets("sheet3").PivotTables("MyOtherPivot")
with pt
{do something}
end with