Collapse all pivot tables in workbook

[p4nny]

Hi,

I've recorded a macro to collapse a pivot table in a workbook.

" Range("B23").Select ActiveSheet.PivotTables("PivotTable6").PivotFields("Account no. & name Concat") _
.ShowDetail = False
Range("B22").Select
ActiveSheet.PivotTables("PivotTable6").PivotFields("Name").ShowDetail = False"




I would now like this applicable to all worksheets in the workbook with the exception of tab called - "MASTER".

Appreciate the help / guidance

Regards, Anthony

 

[ParamRay]

.
.

This macro will collapse all row fields and column fields in all pivot tables in all worksheets, except for in the "MASTER" worksheet:

Sub CollapsePivotFields()
  
  Dim ws As Worksheet
  Dim pt As PivotTable
  Dim rf As PivotField
  Dim cf As PivotField
  
  For Each ws In ActiveWorkbook.Worksheets
    If UCase$(ws.Name) <> "MASTER" Then
      For Each pt In ws.PivotTables
        For Each rf In pt.RowFields
          If rf.Position <> pt.RowFields.Count Then
            rf.ShowDetail = False
          End If
        Next rf
        For Each cf In pt.ColumnFields
          If cf.Position <> pt.ColumnFields.Count Then
            cf.ShowDetail = False
          End If
        Next cf
      Next pt
    End If
  Next ws
  
End Sub

 

[p4nny]

Brilliant- many thanks for this..

One thing, it doesn't seem to work for excluding the Master sheet. Additionally If i wish to exclude 1 more sheet, how would I do this?

Very much appreciated.

 

[ParamRay]

.
.

If your worksheet is named exactly "MASTER" (without any other characters), then the above macro works with no problems.

To exclude other sheets as well, use something like this (making sure to the put sheet name in UPPERCASE):

  .
  .
  .
  If UCase$(ws.Name) <> "MASTER" And UCase$(ws.Name) <> "OTHER SHEET NAME" Then
  .
  .
  .