skip to Main Content

Macros Are Assembly Line Approach for Spreadsheets 
“I approve of your spreadsheet methodology.”
-Henry Ford

I’ve got some Excel VBA macros that you may find useful in your work on PowerPivot.  Been saving these up for awhile now, and I think there are now enough to warrant a post.

I’ll post them all below with a description of each, but since WordPress seems to prevent copy/paste by default, I will also place a text file here containing all of them, so you can download all at once.

These get the job done in workmanlike fashion.  Feel free to point out how much better I could have made them 🙂  I know, I could have turned off visual display updates to make them faster, for instance, but if I had to make these things pretty before sharing, I never would have shared 🙂

So hopefully, you will find them useful, if as nothing more than a starting point.

Without further delay, I give you, the macros…

“Make the same change to all PivotTables in the Workbook” Macro

Useful When:  You have lots of Pivots and they all need the same change made

Be careful of:  Well, as with all macros, you should save your work before running

Sub ModifyAllPivotTables()
    Dim Pivot As PivotTable
    Dim Sheet As Worksheet
    For Each Sheet In ActiveWorkbook.Worksheets
        For Each Pivot In Sheet.PivotTables

            ‘YOUR CODE HERE 
            ‘EXAMPLE:  Add Field “b” to Rows:
            ‘Pivot.AddDataField Pivot.PivotFields(“b”), “Sum of b”, xlSum
            ‘With Pivot.PivotFields(“b”)
            ‘     .Orientation = xlRowField
            ‘     .Position = 3
            ‘End With

End Sub

Remove slicer parent controls (rectangles around slicers in the sheet)

Useful when:  You want to get rid of that “Unsupported Features” warning in Excel Services
Also useful when:  You end up with lots of slicer parent controls stacked on top of each other, like we do…  ‘cuz we copy/paste sheets too much

Be careful of:  This macro nukes all Rectangle art shapes in the workbook 🙂
Also be careful of:  The PowerPivot addin puts the parent controls back whenever you select a pivot

Sub RemoveAllRectangles()
    Dim oSheet As Worksheet
    Dim oShape As Shape
    For Each oSheet In ActiveWorkbook.Sheets
        For Each oShape In oSheet.Shapes
            If Left(oShape.Name, 9) = “Rectangle” Then
            End If
        Next oShape
    Next oSheet
End Sub

Connect all Slicers on a given sheet to a specific pivot on any sheet

Useful when:  You want to create an All Slicers sheet, that contains nothing but slicers that are intended to affect pivots on other sheets.  I like to use the PowerPivot addin to create the slicers because it sizes them correctly, then getting rid of the pivot, leaving just a slicer sheet.  But then you have to connect all those slicers to the pivot(s) on other sheet(s), eek.  This macro is a huge help.

Be careful of:  What could possibly go wrong with this one?  Heh heh.

Sub ConnectSlicers()

   Dim oSlicer As Slicer
   Dim oSlicerCache As SlicerCache

   For Each oSlicerCache In ActiveWorkbook.SlicerCaches
       For Each oSlicer In oSlicerCache.Slicers
           If oSlicer.Shape.BottomRightCell.Worksheet.Name = “YOUR SHEET NAME HERE” Then
               oSlicer.SlicerCache.PivotTables.AddPivotTable (Sheets(“ANOTHER OR SAME
           End If

End Sub

“Disable slicer cross filtering” macro

Useful when:  you have a lot of pivots in a workbook that all have very similar slicer fields in use, and you suddenly realize that cross-filtering is not required on a bunch of those fields and it is just slowing down performance.

Be careful of:  If you have slicer fields with similar names, this macro’s use of InStr (essentially, a “Contains” operator) may cause you problems.

Sub DisableCrossFilter()

Dim oSlicer As Slicer
Dim oSlicerCache As SlicerCache
Dim sName As String
Dim bClearCrossFilter As Boolean

Dim aTestString(1 To 3) As String
Dim iStringCount As Integer
iStringCount = 3

Dim i As Integer

aTestString(1) = “Buyer”
aTestString(2) = “Period”
aTestString(3) = “Store”
For Each oSlicerCache In ActiveWorkbook.SlicerCaches
    For Each oSlicer In oSlicerCache.Slicers
        bClearCrossFilter = False
        For i = 1 To iStringCount
            If InStr(oSlicer.Name, aTestString(i)) > 0 Then
                bClearCrossFilter = True
            End If
        Next i
        If bClearCrossFilter = True Then oSlicer.SlicerCacheLevel.CrossFilterType = xlSlicerNoCrossFilter
End Sub

Enable slicer cross filtering

Useful when:  you over-use the macro above

Just replace the FOR loop body of the macro above with:

For Each oSlicerCache In ActiveWorkbook.SlicerCaches
    For Each oSlicer In oSlicerCache.Slicers
        bSetCrossFilter = False
        For i = 1 To iStringCount
            If InStr(oSlicer.Name, aTestString(i)) > 0 Then
                bSetCrossFilter = True
            End If
        Next i
        If bSetCrossFilter = True Then oSlicer.SlicerCacheLevel.CrossFilterType = 

Hide all gridlines and headers

Useful when:  you want to make your report sheets look more professional

Sub HideGridAndHeaders()

Dim sSheet As Worksheet

For Each sSheet In ActiveWorkbook.Worksheets
    ActiveWindow.DisplayGridlines = False
    ActiveWindow.DisplayHeadings = False

End Sub

Link to text file with all macros

Here’s the link again in case you don’t want to scroll all the way back up.

Got a macro of your own?

Post it in comments or email it to me.  If I get enough I’ll do a “Part Two – Macros From the Community” post.

Rob Collie

Rob Collie

One of the original engineering leaders behind Power BI and Power Pivot during his 14-year career at Microsoft, Rob Collie founded a consulting company in 2013 that is 100% devoted to “the new way forward” made possible by Power BI and its related technologies. Since 2013, PowerPivotPro has rapidly grown to become the leading firm in the industry, pioneering an agile, results-first methodology never before seen in the Business Intelligence space. A sought-after public speaker and author of the #1-selling Power BI book, Rob and his team would like to help you revolutionize your business and your career.

This Post Has 20 Comments
  1. Hey Rob,

    Nice to see some slicer code examples out there. 🙂

    I can’t wait till you can update this page with the VBA required to refresh the PowerPivot data source connections at opening. Now that will be cool. 😉

  2. Thanks for your help, it’s great!

    I have question, in my workbook I have a main sheet of slicers and the other sheets with pivot tables all using these slicers.

    Is there a way to do a selection in the slicers and not refresh any pivot table?

    I could then use a button on the pivot table’s sheets to refresh them using something like:


    1. Well any pivots connected to the slicer will necessarily update on click.

      But what if u connected the slicer to no pivots? And then the button click runs Vba that goes and inspects the “standalone” slicer, notes the selections made in it, and then sets eiher a report filter or a slicer that IS connected to the current pivot? (Sets it to the same selections)

      1. That’s an idea.
        But I saw that selecting a lot of items in a slicer using VBA takes a very long time. I tried that to select a full year of dates (not from january to december), so about 365 items, but it took like 5 minutes to load.
        I used this code with “myArray” filled up correctly:
        ActiveWorkbook.SlicerCaches("Slicer_Date").VisibleSlicerItemsList = myArray

        While debugging I could see that filling up the array was not long at all.

        What do you have in mind about a report filter?
        Is there a way to disconnect and reconnect a pivot using VBA?

      2. I believe the code for setting a report filter to a list of values is basically the same code as setting a slicer to a list of values. So if you add a report filter to the pivot, and then run your array-setting code against it instead, my hope is that would be much faster.

        I’ve never had a problem with setting a slicer to a single value via VBA. I wonder if setting a slicer to an array of values via VBA sets each value one at a time, that would be an unfortunate implementation on MS’s part. How long does it take to set the same slicer, via VBA, to an array of 36 items? If it’s 1/10 as long as it is to set to 365, we may have our answer.

      3. Ok that’s weird, yesterday when I tried that on a full year it was taking forever but today it takes one second.
        Well I will try with this solution of having a sheet of slicers NOT connected to pivots and have some other slicers connected to each pivot refreshed when clicking on a button.

        Thanks for the help.

  3. I am attempting to write a Macro in VBA to at least open powerpivot in excel. In my references I have a ” Microsoft_AnalysisServices_XLHost_Addin” library. Anyone else familiar with this?

  4. Hi There!

    I was wondering if anyone can help me on a problem I have been facing.

    I have a simple pivot table using Power Pivot.

    I want to allow the user to enter a number in a cell block and that should be used to refresh the pivot tables.

    I think a macro can be used.

    Any guidance appreciated.

    ManY Thanks


  5. Hi, I ha a excel 2010 file (tried already VBA enabled Macro format and Binary) with Powerpivot tables and pivot tables.
    I found that I cannot run any macros, even though the Macros ARE enabled, I keep getting the message “Cannot run the macro….. The macro may not be available (it IS) in this workbook or all macros may be disabled”.
    Could it have anything to do with Powerpivot?
    Thanks in advance for any help.Regards, Jose

  6. Is there a way to hide the slicers and keep them hidden? I did a macro to hide the columns they’re in and it worked….. for a second. Then the slippery slicers unchecked the “move and size with cells” on their own and came back…..

    I want to allow the users to make their selections and then be able to hide the slicers to give them more screen real estate to see the data.

  7. I have 2 slicers for a report. Selecting the value in 1st slicer (Region), automatically shortlists the value in 2nd slicer (Invoice Numbers). This 2nd slicer is shared with another worksheet. I want the complete set of automatically shortlisted values in the 2nd slicer in the first worksheet or report to be the slicer conditions in the 2nd worksheet.Slicer values of Invoice numbers gets propagated to the 2nd worksheet only when I select one invoice numbers but the complete set is not working. Can someone help me?

    1. You probably need to connect slicer 1 to the 2nd worksheet as well. Since you haven’t actually MADE a selection on slicer 2, it is NOT impacting worksheet 2. The shortlist behavior is just showing you which slicer values WOULD give you data, were you to click them. But the shortlist behavior is NOT equivalent to then selecting those same shortlisted values yourself with the mouse.

  8. yes, the slicer 1 is not connected to worksheet2 because there is no connection between slicer 1 and fact table used in worksheet2.

  9. I have been trying to figure out the same thing for some time, any help appreciated. At least it’s good to see that PowerPivot can be accessed thru VBA at all…

  10. Does “xlDataField” not work with PowerPivot? I’ve got the code below, which is used to add all measures to the pivot table. It works with “xlColumnField” and “xlRowField”. Any ideas how to make this work?

    Sub AddAllFieldsValues()
    Dim pt As PivotTable
    Dim iCol As Long
    Dim iColEnd As Long

    Set pt = ActiveSheet.PivotTables(1)

    With pt

    iCol = 1
    iColEnd = .CubeFields.Count

    For iCol = 1 To iColEnd
    With .CubeFields(iCol)
    If .Orientation = xlHidden Then
    .Orientation = xlDataField
    End If
    End With
    Next iCol

    End With

    End Sub

  11. Hi,

    I have created a pivot which is created by power query and I tried to create multiple worksheets from the filter values but it shows an error but the code runs fine in the normal pivot, could you please help me to find the solution.

Leave a Reply

Your email address will not be published. Required fields are marked *