Is it possible to assign this VBA by a group? How do you label the group and write the VBA to recognize it as one?
- To post as a guest, your comment is unpublished.· 2 years agoHi Michael,
Sorry can't help with this. Thanks for your comment.
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: