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

[nachelle]

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!

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

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

 

[nachelle]

bumping this thread in case anyone that missed it can help.

 

[nachelle]

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.

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

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]

I think it is a problem with the following section.

   For Each element In MyArrStatus
        ActiveWorkbook.SlicerCaches("Slicer_Status1").SlicerItems(element).Selected = True
    Next element