Auto-Modify Every Pivot in a Workbook

It’s *ALMOST* That Easy

It’s been awhile since I’ve talked about Macros (also known as VBA).  I think it’s overdue.

Macros are nothing short of amazing.  We couldn’t live without them at Pivotstream.

Wait Rob, Macros Don’t Run on the Server!

That’s right, they don’t.  And everything we do at Pivotstream eventually lands on the server.  So why do we use macros?

Simple:  we use macros to more efficiently create and modify our workbooks.  Macros are a “design time” tool for us, not a “run time” tool.

And they have saved us probably decades of work.  I’m not exaggerating.

An Example:  The “Change Every Pivot” Macro

Here’s a pretty simple macro that runs through every PivotTable on every visible worksheet and does whatever you want to each pivot:

Sub ModifyAllPivots()
    Dim Pivot As PivotTable
    Dim Sheet As Worksheet
   
    For Each Sheet In ActiveWorkbook.Worksheets
        If Sheet.Visible = xlSheetVisible Then
            For Each Pivot In Sheet.PivotTables
                AutoPadPivot Sheet.Name, Pivot.Name, 10
                GrandTotalsBottomOnly Sheet.Name, Pivot.Name
            Next
        End If
    Next

End Sub

I emphasized the “payload” of the macro – for each pivot the macro finds, it “pads” the columns of the pivot to be wide enough, and sets the pivot to display grand totals only on the bottom of the pivot (and never on the right).

Those two lines are macros that I also wrote, and I will include them below.

Auto Pad Pivot Columns Macro

At Pivotstream, we tend to ALWAYS turn off the “auto-fit columns on update” setting on our pivots:

Making Pivots NOT Change Column Widths On Slicer Clicks Etc

This yields a much more pleasant “application-like” result – clicking a slicer never results in things jumping around.  I highly recommend it.

But this DOES lead to a problem.  If your numbers suddenly grow by a digit in the future, you can get something like this:

One of the Drawbacks of Turning off Autofit Column Width

To account for this, we have a macro that runs through every pivot in the workbook and “pads” the column width by a percentage:

Sub AutoPadPivot(sSheet As String, sPivot As String, iPct As Integer)
    Dim oPivot As PivotTable
    Dim oSheet As Worksheet
    Dim r As Range
    Dim rCurr As Range
    Dim iCol As Integer
  
    Set oSheet = ActiveWorkbook.Worksheets(sSheet)
    Set oPivot = oSheet.PivotTables(sPivot)
   
    Set r = oPivot.DataBodyRange.Rows(1)
    For Each rCurr In r.Cells
        iCol = rCurr.Column
       
        oSheet.Columns(iCol).EntireColumn.AutoFit
        oSheet.Columns(iCol).ColumnWidth = oSheet.Columns(iCol).ColumnWidth * (1 + (iPct / 100))
    Next
   
   
End Sub

At the beginning of this post, you saw an example where I called this macro to pad each column by 10 percent.  But I can pad by 5, 15, whatever I want.  And you can easily imagine a version that pads by an absolute amount rather than a percentage.

Grand Totals Bottom Only Macro

This one is more self-explanatory:

Sub GrandTotalsBottomOnly(sSheet As String, sPivot As String)
    Dim oPivot As PivotTable
    Dim oSheet As Worksheet
   
    Set oSheet = ActiveWorkbook.Worksheets(sSheet)
    Set oPivot = oSheet.PivotTables(sPivot)
   
    oSheet.PivotTables(sPivot).RowGrand = False
End Sub

Want one more?  OK.  You talked me into it.  We have dozens, many of which are quite ambitious.  Simpler macros make for better blog posts though, so…

Create Page Filter For Each Slicer On Pivot Macro

Sub CreatePageFilterForEachSlicerOnPivot(sSheet As String, sPivot As String)
    Dim oSlicer As Slicer
    Dim oSlicerCache As SlicerCache
    Dim sField As String
    Dim oPivot As PivotTable
    Dim oSheet As Worksheet
   
    Set oSheet = ActiveWorkbook.Sheets(sSheet)
    Set oPivot = oSheet.PivotTables(sPivot)
   
    For Each oSlicer In oPivot.Slicers
        sField = oSlicer.SlicerCache.SourceName
        oPivot.CubeFields(sField).Orientation = xlPageField
    Next
   
End Sub

Why would I want to create a page filter for very slicer on the pivot?  Well, primarily so you can “harvest” the slicer selections in formulas.  But there are other reasons you might do this as well, which I will likely cover in the future.

Learn to Record Macros Folks!

What’s that, you say?  You never need to pad pivots, switch their grand total settings, or add page filters for every slicer?  Not satisfied eh?  Well, you can make your own!

If you have never recorded a macro, seriously, it’s SOOOO easy.  You should try it. 

First you will need to enable the Developer ribbon by going to File|Options|Customize the Ribbon.

Once you’ve done that, here’s how you get started:

Going Into Macro Recording Mode
(Note That I Named the Macro Based On What I Am Going to Do Next)

Now I do something to the pivot.  In this case, I switch my Pivot to one of the “Medium Green” Styles:

Changing My Pivot to a Different Style
(While the Macro Recorder Watches My Every Move)

Now I can stop recording:

Stop Recording the Macro

Inspecting Your Freshly-Recorded Macro

Just click the Macros button, select your macro, and then click Edit

And here it is:

Sub ChangeToGreenStyle()
‘
‘ ChangeToGreenStyle Macro
‘

‘
ActiveSheet.PivotTables(“PivotTable3”).TableStyle2 = “PivotStyleMedium4”
End Sub

There’s really only one line in the macro that DOES anything, so I highlighted it.

Note that it’s “tied” to a pivot named “PivotTable3.”  Not all of your pivots will be named that of course.  And it only works on the ACTIVE sheet.  So it won’t work if you try to loop through using the ModifyAllPivots macro.

So, you can modify it to look like the other macros I showed above (GrandTotalsBottomOnly, etc).  Change the macro to be:

Sub ChangeToGreenStyle (sSheet As String, sPivot As String)

   Dim oPivot As PivotTable
   Dim oSheet As Worksheet

   Set oSheet = ActiveWorkbook.Worksheets(sSheet)
   Set oPivot = oSheet.PivotTables(sPivot)

   oSheet.PivotTables(sPivot).TableStyle2 = “PivotStyleMedium4”

End Sub

The stuff in grey is just copied from the other macros.  The red is a replacement (also copied from the other macros) for the part the recorder set to Active Sheet and PivotTable3.

That macro can now be called from within ModifyEveryPivot just like the others were:

…
      For Each Pivot In Sheet.PivotTables
         AutoPadPivot Sheet.Name, Pivot.Name, 10
         GrandTotalsBottomOnly Sheet.Name, Pivot.Name
         ChangeToGreenStyle Sheet.Name, Pivot.Name
      Next
…

Off you go