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.
Keep slicer of Pivot Table moving with worksheet scrolling with VBA code
Keep slicer of Pivot Table moving with worksheet scrolling with VBA code
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 SubNotes:
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:
Related articles:
- 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?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
