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.
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.
_______
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.
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
I’ve noticed that multiple slicers stay together better if they’re grouped together using slicer tools -> group.
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.
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
Thanks Ron, that’s a nice addition!