VBA - create separate spreadsheets from a filtered table The task is relatively simple and until now it was always performed by some poor admin soul.  There is a large table that needs to be filtered based on a simple criteria and each view has to be saved as a new spreadsheet.
The code is as simple as the task (and as my coding skills) so I would love to get some feedback about any tricks I might have missed to make it more robust or &quot;best practices&quot; advice.
Option Explicit

Sub SplitWorksheet()

    Dim d As Long
    Dim dctList As Object
    Dim varList As Variant
    Dim varName As Variant
    Dim wkb As Workbook
    Dim wks As Worksheet
    Dim rng As Range
    Dim wkbNew As Workbook
    Dim strPath As String

    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    
    Set wkb = ThisWorkbook
    Set wks = wkb.Sheets(&quot;Data&quot;)
    Set rng = wks.Range(&quot;A1&quot;).CurrentRegion
    
    strPath = Application.ThisWorkbook.Path &amp; &quot;\Distribution\&quot;
        
    Set dctList = CreateObject(&quot;Scripting.Dictionary&quot;)
    dctList.CompareMode = vbTextCompare

    With wks
    
        varList = .Range(.Cells(6, &quot;H&quot;), .Cells(Rows.Count, &quot;H&quot;).End(xlUp)).Value2

        For d = LBound(varList) To UBound(varList)
            dctList.Item(varList(d, 1)) = vbNullString
        Next

        For Each varName In dctList
            .Range(&quot;a1&quot;).CurrentRegion.AutoFilter Field:=8, Criteria1:=&quot;=&quot; &amp; varName, Operator:=xlFilterValues
        
            Set wkbNew = Workbooks.Add
            .Range(&quot;A1&quot;).CurrentRegion.SpecialCells(xlCellTypeVisible).Copy _
                Destination:=wkbNew.Sheets(1).Range(&quot;A1&quot;)
            wkbNew.SaveAs strPath &amp; varName &amp; &quot;.xlsx&quot;
            wkbNew.Close
        
        Next
        
        .Range(&quot;a1&quot;).CurrentRegion.AutoFilter
        
    End With

    Application.DisplayAlerts = True
    Application.ScreenUpdating = True

    MsgBox &quot;All done, individual spreadsheets have been saved&quot;, vbOKOnly, &quot;Great success!&quot;
 
End Sub
I think Power Query (aka Get &amp; Transform) could do this much more straightforwardly. Two queries one filtering for one thing another for the other they could live in the same or two different workbooks - refresh and save as, done. You could VBA the bit that saves them into separate books and names them in various ways I guess.