Debra Dalgleish - Pivot Tables (Change Multiple Page Fields)

[Guest]

Hi Debra, I saw the sample spreadsheet on your website and was wondering if
the code could be used if the pivot tables are not based on the same data
but have the same page fields. Is this possible?

 

[Debra Dalgleish]

That code might cause a problem if the items in the fields are
different. I've uploaded a version that may work better, and could be
used if the field names are different too.

http://www.contextures.com/excelfiles.html

Under PivotTables, look for 'PT0015 - Change Multiple Different Page Fields'

 

[Guest]

Debra, thank you, I'll take a look at it.

P.S. I love your site and have learned so much from it already. Thank you.

 

[Guest]

Hi Debora,

I went to your website and found one of your pivot samples helpful. It was
the one where you could update page field values on a sheet named “Sales
Pivot†and this caused the field names on your three pivot tables contained
in the sheet “Other Pivots†to change accordingly.

My problem is that I have six pivot tables that are contained in six
individual worksheets. I am attempting to alter the code to reflect this
diffent setup, but I fear that I won’t be successful. I consider myself a
beginner in VBA coding, so an explanation would have to be fairly
straightforward.

However, I realize that you are busy and might not have the time to give me
a complete solution. Any direction would be immensely appreciated! Here is
the code (with extra formatting code omitted) that updates six pivot tables
in a workbook in their respective worksheets.

Sub Main()
Dim PctDone As Single

For Each ws In ThisWorkbook.Worksheets
ws.Unprotect Password:="1111"
Next ws

For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
pt.PivotCache.Refresh
Next pt
Next ws

Application.Goto reference:="Data_Home"
Selection.QueryTable.Refresh BackgroundQuery:=False

Application.Goto reference:="ALL_HOME"
ActiveSheet.PivotTables("ALL_TABLE").PivotCache.Refresh

Application.Goto reference:="BASE_HOME"
ActiveSheet.PivotTables("BASE_TABLE").PivotCache.Refresh

Application.Goto reference:="IP_HOME"
ActiveSheet.PivotTables("IP_TABLE").PivotCache.Refresh

Application.Goto reference:="CORP_HOME"
ActiveSheet.PivotTables("CORP_TABLE").PivotCache.Refresh

Application.Goto reference:="CBSA_HOME"
ActiveSheet.PivotTables("CBSA_TABLE").PivotCache.Refresh

Application.Goto reference:="IBC_HOME"
ActiveSheet.PivotTables("IBC_TABLE").PivotCache.Refresh

For Each ws In ThisWorkbook.Worksheets
ws.Protect Password:="1111", DrawingObjects:=True, Contents:=True,
Scenarios:=True _
, AllowUsingPivotTables:=False
Next ws

Application.Goto reference:="Summary_Home"
ActiveWorkbook.Save

End Sub

Any help would be appreciated!
Cheers,
Kent Lysell