How to keep slicer of Pivot Table moving with worksheet scrolling in Excel?
While working with Pivot Table, you may insert slicers to filter data visually of the table. This article is talking about keeping slicer of Pivot Table always visible while scrolling the worksheet.
The following VBA script can help you to keep slicer of Pivot Table moving with worksheet. Please do as follows.
1. Press the Alt + F11 keys simultaneously to open the Microsoft Visual Basic for Applications window.
2. In the Microsoft Visual Basic for Applications window, double click ThisWorkbook in the left Project pane, and then copy and paste the below VBA code into the ThisWorkbook (Code) window. See screenshot:
VBA code: Keep slicer of Pivot Table moving with worksheet scrolling
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) Dim ShF As Shape Dim ShM As Shape 'specify a slicer Application.ScreenUpdating = False Set ShF = ActiveSheet.Shapes("Column1") Set ShM = ActiveSheet.Shapes("Column2") 'change position of the slicer With Windows(1).VisibleRange.Cells(1, 1) ShF.Top = .Top ShF.Left = .Left + 300 ShM.Top = .Top ShM.Left = .Left + 100 End With Application.ScreenUpdating = True End Sub
1). In the code, Column1 and Column2 are names of the slicers.
2). You can specify the position of slicers while scrolling the worksheet in the code.
3). And you can add more slicers into the code or remove slicers from it as you need.
3. Press the Alt + Q keys to close the Microsoft Visual Basic for Applications window dialog box.
From now on, the specified slicers will be moved with the active cell while scrolling the worksheet. See screenshot:
- How to keep table expandable by inserting table row in a protected worksheet in Excel?
- How to combine cells and keep the cell formatting in Excel?
- How to remove duplicates but keep first instance in Excel?