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