Macro to Move a Pivot Table Slicer

macro to move a pivot table slicer Recently, we saw how you can use Excel Slicers, to filter fields in one or more pivot tables. This week, we’ll use a macro to move a pivot table slicer. In the comments of the previous article, James asked how to keep those Slicers from overlapping the pivot tables.

  • Does anyone know how to stop slicers moving around when you make selections. This happens if the slicers are viewed on top of the pivot table data. As the pivot output data shrinks or expands the slicers move around and sometimes obscure each other. Any idea how to fix in place?

Pivot Table Update Event

One way to fix the problem of sliding Slicers is to automatically move the Slicers, any time the pivot table is updated. To do that, you can use the PivotTableUpdate event, and a macro that moves the Slicer to the right side of the pivot table.
Each Slicer has a caption, and you can refer the the Slicer by that caption in the code. In this example, the Slicer has a caption of “Region”, which you can see at the top of the Slicer. The caption is also visible on the Excel Ribbon’s Options tab, when the Slicer is selected.
macro to move a pivot table slicer

Macro to Move a Pivot Table Slicer

Here is the sample code that I used — a macro to move a pivot table slicer to the right side of the pivot table, any time the pivot table is updated. This code is stored on a regular code module.
In the code, a variable is set for the pivot table, and the code counts the columns in the pivot table’s TableRange2 range, which includes the Report Filters area. (TableRange1 does not include the report filters.)
We add 1 to the column number that the last pivot table column is in.
A set amount of padding is added (10 in this example), to indent the Slicer by that amount in the column to the right of the pivot table.

Sub MoveSlicer()
    Dim wsPT As Worksheet
    Dim pt As PivotTable
    Dim sh As Shape
    Dim rngSh As Range
    Dim lColPT As Long
    Dim lCol As Long
    Dim lPad As Long
    Set wsPT = Worksheets("PivotSales")
    Set pt = wsPT.PivotTables("PivotDate")
    Set sh = wsPT.Shapes("Region")
    lPad = 10
    lColPT = pt.TableRange2.Columns.Count
    lCol = pt.TableRange2.Columns(lColPT).Column
    Set rngSh = wsPT.Cells(1, lCol + 1)
    sh.Left = rngSh.Left + lPad
End Sub

Pivot Table Update Code

The following code should be copied to the pivot table’s worksheet module. It will run the macro to move a pivot table slicer (MoveSlicer), any time the PivotDate pivot table is updated.

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    If Target.Name = "PivotDate" Then
        MoveSlicer
    End If
End Sub

Download the Sample File

To see how the macro to move a pivot table slicer works, you can download the Excel Slicer Move Code sample workbook. The file is in xlsm format, and is zipped. You’ll have to enable macros, to test the code.
_______