VBA Store Slicer Selection, Run Macros, Re-Select Slicers

nachelle

New Member
Joined
Mar 11, 2020
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
Good morning, all!

I have been stuck on this issue for days and hope someone can help.

I have a table on Sheet2 [Sheet2.Range("A2:O" & LastRow)] that gets copied to a table on Sheet1 [Sheet1.Range("Table1")] through a macro. The table on Sheet 1 starts on D17 and goes to the last row in the V column (Columns S:V are formulas to analyse data copied from Sheet2). Table1 has 2 slicers filtering on 2 columns. A macro (srtnc) sorts the table based on column V.

My goal: be able to use both slicers to filter data in table1 and maintain that filter through the copy and sort macros. Currently, if the table is "refreshed" while slicers are selected, something goes wrong. I want to find a way to store the selected elements for the slicers, clear the slicers, run the refresh & sort macros, re-select previously selected slicer elements.

I am a relatively new VBA user and have tried all the methods that I know. I have attached the Refresh and Sort macros below. I know this is probably a crazy request. Please, help if you can!

VBA Code:
Sub RefreshContactTable()
Dim myRange As Range
Dim tbl As ListObject

Set tbl = Application.Range("Table1").ListObject
Application.ScreenUpdating = False
Set myRange = ActiveCell
UpdateFile
LastRow = Sheet2.Range("A99999").End(xlUp).Row
Sheet2.Range("A2:O" & LastRow).Copy
Sheet1.Range("Table1").PasteSpecial Paste:=xlPasteValues
Application.ScreenUpdating = True
myRange.Select
End Sub

VBA Code:
Sub srtnc()

    ActiveWorkbook.Worksheets("Non-Comp").ListObjects("Table1").Sort.SortFields. _
        Clear
    ActiveWorkbook.Worksheets("Non-Comp").ListObjects("Table1").Sort.SortFields. _
        Add2 Key:=Range("Table1[Day(s) Old]"), SortOn:=xlSortOnValues, Order:= _
        xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Non-Comp").ListObjects("Table1").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

nachelle

New Member
Joined
Mar 11, 2020
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
bumping this thread in case anyone that missed it can help.
 

nachelle

New Member
Joined
Mar 11, 2020
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
Update:

Currently trying to get this to work using a function to retrieve the selected items. Then, clear slicer selection, sort, and select items in array from function. It is still not selecting and I don't know where it is going wrong. :eek:

VBA Code:
Public Function ArrayListOfSelectedAndVisibleSlicerItems(MySlicerName As String) As Variant

    Dim ShortList() As Variant
    Dim i As Integer: i = 0 'for iterate

    Dim sC As SlicerCache
    Dim sI As SlicerItem 'for iterate

    Set sC = ThisWorkbook.Application.ActiveWorkbook.SlicerCaches(MySlicerName)
    For Each sI In sC.SlicerItems
        If sI.Selected = True And sI.HasData = True Then 'Here is the condition!!!
                'Debug.Print sI.Name
            ReDim Preserve ShortList(i)
            ShortList(i) = sI.Value
            i = i + 1
        End If
    Next sI
    ArrayListOfSelectedAndVisibleSlicerItems = ShortList
End Function

VBA Code:
Sub GetSlicerNCSTatusSel()
    Dim MyArrStatus() As Variant
    
    MyArrStatus = ArrayListOfSelectedAndVisibleSlicerItems("Slicer_Status1")  
    
    Dim slcr As SlicerCache
    Dim slc  As Slicer
    
    Dim element As Variant
      
    Application.ScreenUpdating = False
     
    For Each slcr In ActiveWorkbook.SlicerCaches
        For Each slc In slcr.Slicers
            If slc.Shape.Parent Is ActiveSheet Then
                slcr.ClearManualFilter
                
                
                Exit For
            End If
        Next slc
    Next slcr
    
srtnc 'sort macro
  
    For Each element In MyArrStatus
        ActiveWorkbook.SlicerCaches("Slicer_Status1").SlicerItems(element).Selected = True
    Next element

   Application.ScreenUpdating = True
   
End Sub
 

nachelle

New Member
Joined
Mar 11, 2020
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
I think it is a problem with the following section.
VBA Code:
   For Each element In MyArrStatus
        ActiveWorkbook.SlicerCaches("Slicer_Status1").SlicerItems(element).Selected = True
    Next element
 

Watch MrExcel Video

Forum statistics

Threads
1,127,405
Messages
5,624,574
Members
416,036
Latest member
eloisa manzanarez

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top