using a wildcard to prevent subtotal line from being filtered and copied excel vba

Question

I have a situation where I have a list of salespeople that gets filtered and moves all that filtered data to it's own spreadsheet. The problem that I am having is the macro is also filtering the subtotal line so its creating a sheet with no data and it's also creating a situation where the sheet is so large I cant save the file.

I wrote some code that i thought would prevent any worksheet starting with "Sheet" to not get filtered, but I don't know how to use a wildcard in a string. Need a wildcard since the "Sheet #" is different depending on the month.

Dim Sht As Worksheet
        Dim Rng As Range
        Dim List As Collection
        Dim varValue As Variant
        Dim E As Long

    '   // Set your Sheet name
        Set Sht = Application.ActiveSheet

    '   // set your auto-filter,  A6
        With Sht.Range("A2")
            .AutoFilter
        End With

    '   // Set your agent Column range # (2) that you want to filter it
        Set Rng = Range(Sht.AutoFilter.Range.Columns(22).Address)


ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Add 
Key:=Range _
         ("V:V"), SortOn:=xlSortOnValues, Order:=xlAscending, 
DataOption:= _
            xlSortTextAsNumbers
        With ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With

    '   // Create a new Collection Object
        Set List = New Collection

    '   // Fill Collection with Unique Values
        On Error Resume Next
        For E = 2 To Rng.Rows.Count
            List.Add Rng.Cells(E, 1), CStr(Rng.Cells(E, 1))
        Next E

    '   // Start looping in through the collection Values
        For Each varValue In List


    '       // Filter the Autofilter to macth the current Value
            'Rng.AutoFilter Field:=22, Criteria1:=varValue, _
             '   Operator:=xlAnd, Criteria2:="<>"

            Rng.AutoFilter Field:=22, Criteria1:="<>Sheet*", _
                Operator:=xlAnd, Criteria2:=varValue

    '       // Copy the AutoFiltered Range to new Workbook
            'If List = (Blanks) Then
            Sht.AutoFilter.Range.Copy

The Criteria1:="<>Sheet*" code is what I tried to do and the code above is what is was before. So my question is what can be done to prevent to the subtotal row sheet from being created?

Answer 1

So after playing with the code for awhile I realized that using a wildcard for "Sheet*" wouldn't work since all the filtered results started with "Sheet" in the first place. But by adding

If varValue <> "" Then (which states that if the filtered result isn't blank than continue code) after Rng.AutoFilter Field:=22, Criteria1:="<>Sheet*", Operator:=xlAnd, Criteria2:=varValue

it fixed the issue. Now the code skips the subtotal line successfully.