Using an Array & Autofilters - VBA

[spydey]

So thanks to a number of kind people here, I have been guided and helped a number of times. Thank you to those that have done so and been patient with my questions as well as lack of understanding. I am learning and your guidance challenges me to learn me. Thank you!

I have a scenario where I define an array from my Main Workbook "Summary" sheet. I then copy the whole workbook and use the copied workbook as my active workbook. I proceed to filter out (delete) the rows that don't match my current variables of my previously defined array. I then save the workbook based upon my two array items to a previously defined location. I then close the active workbook and make a copy of the Main workbook and go through it all again, but having moved down one item in my array set. Hopefully that makes sense.

The issue I am running into is that when I get to the second filter, I receive an error message. I believe that it might be due to having deleted rows after my first filter and thus the array I am referring to in my second filter no longer matches my activeworkbook range. (??????)

My thought is that I either need to redim my array, but then I would lose the location (order) of my original array (unless I preserve it???), or to do both filters at the same time and then delete the remaining items, which I haven't figured out yet.

Any thoughts?

Below is my code thus far:

Sub FilterOutArray()


Dim rng As Range
Dim Tracking As Variant
Dim strPath As String
Dim strFileName As String
Dim i As Long
Dim ws As Worksheet




    Set rng = ThisWorkbook.Worksheets("Summary").Range("A3:B15")
       
    Tracking = rng.Value


    strPath = "C:\Location\Where\The\Files\Are\To\Be\Saved\"
    
    For i = LBound(Tracking) To UBound(Tracking)
    
    Application.DisplayAlerts = False
    
    ThisWorkbook.Worksheets.Copy
    
            
    Set ws = ActiveWorkbook.Worksheets("Summary")
                
    With ws
        .AutoFilterMode = False
        .Rows("2:2").AutoFilter
        .Range("Items_Summary").AutoFilter Field:=1, Criteria1:="<>" & Tracking(i, 1)
        .UsedRange.Offset(2, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        .AutoFilterMode = False
        .Rows("2:2").AutoFilter
[B]        .Range("Location_Summary").AutoFilter Field:=2, Criteria1:="<>" & Tracking(i, 2)[/B]
        .UsedRange.Offset(2, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End With


    ws.AutoFilterMode = False




   
        strFileName = Tracking(i, 1) & " - " & Tracking(i, 2)
        ActiveWorkbook.SaveAs filename:=strPath & strFileName, fileformat:=52
        
    ActiveWorkbook.Close


    Next i
    
    Application.DisplayAlerts = True


End Sub

I have bolded the line of code above that keeps hanging.

Thanks again to everyone for their patience, understanding, assistance, and willingness to help me learn. It is greatly appreciated!!

-Spydey

 

[spydey]

Actually, after posting this, I realized (after hours of pouring over it) that I think I had the incorrect named range in my second filter ............ I have corrected it in my above code, will test it, and report back.

-Spydey

 

[Fluff]

What range does "States_AttyData" refer to?
If it's 1 column then the filter should be Field:=1

 

[spydey]

What range does "States_AttyData" refer to?
If it's 1 column then the filter should be Field:=1

Thanks Fluff for the response.

I figured out that for my second filter I was calling the wrong named range from the wrong sheet and the wrong field number .... just all wrong .... lol

I corrected it this morning and it runs well. Now on to expanding it to the other sheets!

Again, thanks for taking the time to take a look at it, I appreciate it.

-Spydey