Excel VBA Macro - Filter Information, Create New Workbook

md457

New Member
Joined
Sep 13, 2021
Messages
2
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,596
Office Version
  1. 365
Platform
  1. Windows
Does the first macro run ok & just the remaining ones copy only the header?
 

md457

New Member
Joined
Sep 13, 2021
Messages
2
Office Version
  1. 2019
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,596
Office Version
  1. 365
Platform
  1. Windows
That's probably because the sheet with the data is no longer the active sheet.
Try doing it like
VBA Code:
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
 
Solution

Forum statistics

Threads
1,147,518
Messages
5,741,640
Members
423,675
Latest member
Dea21

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top