Excel VBA Macro - Filter Information, Create New Workbook

[md457]

I have a large data set that I need to filter for category and use. That is to say, filter column D and then filter column E. D is the category and E is the use. Category A has 3 uses, Category B has 4 uses, Category C has 17 uses and Category D has 4 uses. I filter by category then individually each use, copy the worksheet and paste it into a new workbook to place into a specific folder for use for a total of 28 workbooks. Manually this takes time and is annoying. I created 28 macros to run each filter and paste the workbook. This works but is also tedious. I tried to create a macro to run all of the macros at once however, it only copies the first row when done this way.

My 28 macros all look fairly the same, the only difference is in the Criterias, being CategoryA...CategoryB... UseA...UseB... etc

Sub CategoryAUseA ()

ActiveSheet.Range("$A$1:$CA$651090").AutoFilter Field:=4, Criteria1:="CategoryA"
ActiveSheet.Range("$A$1:$CA$651090").AutoFilter Field:=5, Criteria1:="UseA"

If ActiveSheet.AutoFilterMode = False Then
Exit Sub
End If

ActiveSheet.AutoFilter.Range.Copy
Workbooks.Add
Worksheets(1).Paste

End Sub

My Macro to run all of them looks like this

Sub RunMacros ()

Application.Run "CategoryAUseA"
Application.Run "CategoryAUseB"
etc
etc

End Sub

I have tried to run this as "Call" but that does not work either.

Is there a way to have the filters run, the sheet copied and then pasted into a new workbook? I have tried pausing the macros (when run in the RunMacros) to see if a pause before a new filter is run would help, having the macros unfiltered but the result is the same, in that the new workbooks only copy over the first row and no other data.

 

[Fluff]

Does the first macro run ok & just the remaining ones copy only the header?

 

[md457]

That is correct, when I run the "RunMacros", the first macro is run fine. The workbook opens with the top row only but if unfiltered has what I want there but the other 27 only have the top row with no data to unfilter.

 

[Fluff]

That's probably because the sheet with the data is no longer the active sheet.
Try doing it like

Sub md()
   Dim Ary As Variant
   Dim i As Long, j As Long
   
   Ary = Array("CategoryA", Array("UseA", "UseB", "UseC"), "CategoryB", Array("UseA", "UseB", "UseC", "UseD"))
   With ActiveSheet
      For i = 0 To UBound(Ary) Step 2
         For j = 0 To UBound(Ary(i + 1))
            .Range("$A$1:$CA$651090").AutoFilter 4, Ary(i)
            .Range("$A$1:$CA$651090").AutoFilter 5, Ary(i + 1)(j)
            Workbooks.Add
            .AutoFilter.Range.Copy
            Sheets(1).Paste
         Next j
      Next i
   End With
End Sub