Macro to Move 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.

Overlapping Slicers

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.

Slicer Caption

Each Slicer has a caption, and you can refer the the Slicer by that caption in the Excel VBA code.

In this example, the Slicer has a caption of “Region”, which is shown at the top of the Slicer.

The caption is also visible on the Excel Ribbon’s Options tab, when the Slicer is selected.

Slicer caption visible on Excel Ribbon's Options tab
Slicer caption visible on Excel Ribbon’s Options tab

Macro to Move a Pivot Table Slicer

Here is the sample code that I used.

This macro moves a pivot table slicer to the right side of the pivot table, any time the pivot table is updated.

Note: This code is stored on a regular code module.

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

How the Code Works

In the code, a variable (pt) is set for the pivot table.

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.)

  • lColPT = pt.TableRange2.Columns.Count

The code adds 1 to the column number that the last pivot table column is in.

  • Set rngSh = wsPT.Cells(1, lCol + 1)

A variable (lPad) sets the padding number — how much the Slicer will be moved to the right. In this example, the variable is set to 10

  • lPad = 10

Finally, the Slicer is positioned, in the column to the right of the pivot table. In that column, the Slicer’s left side is indented by the padding amount.

  • sh.Left = rngSh.Left + lPad

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.
_______

0 thoughts on “Macro to Move Pivot Table Slicer”

  1. Thanks for above tutorial. The guidelines are excellent. But novice here want to know:
    1- if more than one pivottable could be moved through above code.
    2- What are TableRange1 & TableRange2 ranges and what do they refer to in PivotSales.
    3- I use Excel 2010. Is above code compatible with it?

    I have tried to modify above code as per my requirment.

    ————––
    Sub MoveSlicer()
    ‘www.contextures.com
    Dim wsPT As Worksheet
    Dim pt1 As PivotTable
    Dim pt2 As PivotTable
    Dim pt3 As PivotTable
    Dim sh1 As Shape
    Dim sh2 As Shape
    Dim sh3 As Shape
    Dim rngSh As Range
    Dim lColPT As Long
    Dim lCol As Long
    Dim lPad As Long

    Set wsPT = Worksheets(“Summary”)
    Set pt1 = wsPT.PivotTables(“PivotTable8?)
    Set pt2 = wsPT.PivotTables(“PivotTable9?)
    Set pt3 = wsPT.PivotTables(“PivotTable10?)
    Set sh1 = wsPT.Shapes(“CPR NO.”)
    Set sh2 = wsPT.Shapes(“CPR NO. 1?)
    Set sh3 = wsPT.Shapes(“CPR NO. 2?)
    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

    Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    ‘www.contextures.com
    If Target.Name = “PivotTable8? Then
    MoveSlicer
    If Target.Name = “PivotTable9? Then
    MoveSlicer
    If Target.Name = “PivotTable10? Then
    MoveSlicer
    End If
    End Sub
    ————––

    Please tell me if it’s any good. TIA
    Regards.

  2. I got the code to work and found answers as well (good for me 🙂 but it does not look neat. Needs tidying up. Please help me out. Here is the code:
    ——————–
    Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    ‘www.contextures.com
    If Target.Name = “PivotTable8? Then
    MoveSlicer
    End If
    If Target.Name = “PivotTable9? Then
    MoveSlicer
    End If
    If Target.Name = “PivotTable10? Then
    MoveSlicer
    End If
    End Sub

    Sub MoveSlicer()
    ‘www.contextures.com
    Dim wsPT As Worksheet
    Dim pt1 As PivotTable
    Dim pt2 As PivotTable
    Dim pt3 As PivotTable
    Dim sh1 As Shape
    Dim sh2 As Shape
    Dim sh3 As Shape
    Dim rngSh1 As Range
    Dim rngSh2 As Range
    Dim rngSh3 As Range
    Dim lColPT1 As Long
    Dim lColPT2 As Long
    Dim lColPT3 As Long
    Dim lCol1 As Long
    Dim lCol2 As Long
    Dim lCol3 As Long
    Dim lPad1 As Long
    Dim lPad2 As Long
    Dim lPad3 As Long

    Set wsPT = Worksheets(“Summary”)
    Set pt1 = wsPT.PivotTables(“PivotTable8?)
    Set pt2 = wsPT.PivotTables(“PivotTable9?)
    Set pt3 = wsPT.PivotTables(“PivotTable10?)
    Set sh1 = wsPT.Shapes(“CPR NO.”)
    Set sh2 = wsPT.Shapes(“CPR NO. 1?)
    Set sh3 = wsPT.Shapes(“CPR NO. 2?)
    lPad1 = 10
    lPad2 = 10
    lPad3 = 10
    lColPT1 = pt1.TableRange2.Columns.Count
    lCol1 = pt1.TableRange2.Columns(lColPT1).Column
    lColPT2 = pt2.TableRange2.Columns.Count
    lCol2 = pt2.TableRange2.Columns(lColPT2).Column
    lColPT3 = pt3.TableRange2.Columns.Count
    lCol3 = pt3.TableRange2.Columns(lColPT3).Column

    Set rngSh1 = wsPT.Cells(1, lCol1 + 1)
    Set rngSh2 = wsPT.Cells(1, lCol2 + 1)
    Set rngSh3 = wsPT.Cells(1, lCol3 + 1)
    sh1.Left = rngSh1.Left + lPad1
    sh2.Left = rngSh2.Left + lPad2
    sh3.Left = rngSh3.Left + lPad3

    End Sub
    ——————–
    TIA
    Regards

  3. I’ve noticed that multiple slicers stay together better if they’re grouped together using slicer tools -> group.

  4. I usually insert an empty column on the left in front of the pivot table. Then I drag the slicers over to the left side, Align and Group.
    I also set the pivot table to not automatically re-size. Pivot Table Options > Layout&Format > Uncheck Autofit column widths on update. Helps my eyes not having to bounce around on the screen.
    Or if the formatting permits I make the slicers really wide 10+ columns and short 1 row deep. And place them at the top of the table. Then I freeze frames at the appropriate cell so the slicers stay on screen when scrolling to the data below.

  5. Hi Debra
    Thank you for posting this. I have expanded on it to change the top of the slicer to the top of the visible row on the screen so when I scroll down the pivot table the slicer moves with the selection.

    Public 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
    Dim lRow As Long
    Set wsPT = Worksheets(“Pivot”)
    Set pt = wsPT.PivotTables(“ExcSpend”)
    Set sh = wsPT.Shapes(“AccName”)
    lPad = 10
    lColPT = pt.TableRange2.Columns.Count
    lCol = pt.TableRange2.Columns(lColPT).Column
    lRow = ActiveWindow.ScrollRow ‘ added line for picking out the top row on the screen
    Set rngSh = wsPT.Cells(lRow, lCol + 1) ‘ included the top row on the screen to the range setting
    sh.Left = rngSh.Left + lPad
    sh.Top = rngSh.Top ‘ adjusted the top of the slicer accordingly.
    end sub

    Thanks again
    Ron

Leave a Reply to Check Multiple Criteria with Excel INDEX and MATCH « Contextures … | Excel Needs Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.