Macro 60: Create a New Sheet for Each Item in an AutoFilter
One of the most common tasks an Excel user is confronted with is separating a data set into separate
sheets. For instance, if you have a set of data that has rows for the east, west, south, and north
regions of the U.S., you may be asked to create a new sheet for the east data, a new sheet for the
west data, a new sheet for the south, and one for the north. In these situations, you would normally
have to manually filter each region, and then copy and paste the data into new sheets. This can be
quite a painful exercise if you have to do it one time. If you have to perform this same exercise on an
ongoing basis? Well, let’s just say it can be difficult to come to work.
The good news is that you can use a macro to do the heavy lifting for you.
How it works
The basic premise of this macro is in itself simple. We start with a data set that contains an AutoFilter
(similar to the one shown in Figure 5-6).
Figure 5-6: Start with a data set that has an AutoFilter applied.
We point the macro to the field that is used to separate the data into separate sheets. In this case, we
need to create a separate sheet for each region. As you can see in Figure 5-6, the Region field is the
first field in the AutoFiltered data set.
The macro goes through this field, capturing the unique data items in this field (North, South, East,
West). Then one at a time, it uses each unique data item as the filter criteria on the AutoFilter, effec-
tively filtering the data for that item.
Each time a region is filtered, the macro copies the filtered range and pastes the data into a new
sheet. After the data is pasted, it names the sheet the same name as the filter criteria.
This macro is a little tough to look at first glance, so take the time to walk through each step
in detail.
Sub Macro60()
‘Step 1: Declare your Variables
Dim MySheet As Worksheet
Dim MyRange As Range
Dim UList As Collection
Dim UListValue As Variant
Dim i As Long
‘Step 2: Set the Sheet that contains the AutoFilter
Set MySheet = ActiveSheet
‘Step 3: If the sheet is not auto-filtered, then exit
If MySheet.AutoFilterMode = False Then
Exit Sub
End If
‘Step 4: Specify the Column # that holds the data you want filtered
Set MyRange = Range(MySheet.AutoFilter.Range.Columns(1).Address)
‘Step 5: Create a new Collection Object
Set UList = New Collection
‘Step 6: Fill the Collection Object with Unique Values
On Error Resume Next
For i = 2 To MyRange.Rows.Count
UList.Add MyRange.Cells(i, 1), CStr(MyRange.Cells(i, 1))
Next i
On Error GoTo 0
‘Step 7: Start looping in through the collection Values
For Each UListValue In UList
‘Step 8: Delete any Sheets that may have been previously created
On Error Resume Next
Application.DisplayAlerts = False
Sheets(CStr(UListValue)).Delete
Application.DisplayAlerts = True
On Error GoTo 0
‘Step 9: Filter the AutoFilter to match the current
Value MyRange.AutoFilter Field:=1,
Criteria1:=UListValue
‘Step 10: Copy the AutoFiltered Range to new Sheet
MySheet.AutoFilter.Range.Copy
Worksheets.Add.Paste
ActiveSheet.Name = Left(UListValue, 30)
Cells.EntireColumn.AutoFit
‘Step 11: Loop back to get the next collection Value
Next UListValue
‘Step 12: Go back to main Sheet and removed filters
MySheet.AutoFilter.ShowAllData
MySheet.Select
End Sub
1. Step 1 starts the macro by declaring five variables. MySheet is a worksheet variable that is used
to identify the sheet in which the AutoFiltered data resides. MyRange is a range variable that
holds the range of our main filter field (the Region field in this scenario). UList is a Collection
object that helps us extract the unique items from our main filter field. UListValue holds the
individual unique items as we enumerate through them. Finally, the i variable serves as a simple
counter for our MyRange variable.
2. Step 2 sets the MySheet variable to hold the sheet in which the AutoFilter resides. It’s
important to do this because we need to refer back to this sheet throughout the macro.
Here, we are assuming the macro will be fired from the sheet that holds the AutoFilter, so we
use ActiveSheet.
You can also alter the macro to explicitly use a sheet name instead of ActiveSheet, like
Set MySheet = Sheets(“YourSheetName”). This is safer because you have no risk
of unintentionally firing the macro from the wrong sheet. But it essentially ensures that the
macro only works for the sheet you explicitly specified.
3. Step 3 checks the AutoFilterMode property to see if the sheet even has AutoFilters
applied. If not, it exits the procedure.
4. If the macro reaches Step 4, we have determined that there is indeed an AutoFilter applied in
MySheet.
Now we need to capture the column number that holds the items that will be used to parse
our data set into separate sheets. As you can see, in Figure 5-6, the region column is the first
column in our AutoFilter range. So we set the MyRange field to Columns(1) of the
AutoFilter range. This is important! We eventually use the specified column to create a
unique list of items with which we parse our data. When you implement this macro in your
environment, you need to change the column number used to match the field you need to
parse.
5. Step 5 initializes the UList Collection object. A Collection object is a container that
can hold an array of unique data items.
In fact, a Collection object can only hold unique data. If you try to fill it with non-unique
data, it throws an error. Because of this, it makes for an excellent way to quickly find and
store a list of unique data items.
We use the collection object to hold a unique list of items from our MyRange variable. In this
scenario, because our MyRange variable points to the Region column, the Collection
object eventually holds a unique list of regions (East, North, South, West).
6. Step 6 fills the UList Collection object with the unique data items in MyRange.
To do so, it uses the i variable to loop through the rows of the MyRange column. You’ll notice
that we start i at 2; this is because row 1 contains the header label (Region). We don’t want to
include the header label as one of the unique items in our collection object.
On each loop, the macro tries to add the current cell to the UList collection. The syntax to
add an item to a collection is
CollectionName.Add ItemName, UniqueKeyIdentifier
In this case, we are adding each cell in MyRange as both the item name and unique key
identifier. Because the UList collection throws an error if the data items are not unique, we
wrap the entire section in On Error Resume Next and On Error Goto 0. This
ensures that if duplicate items are added, the UList collection ignores them. At the end of
the loop, we have a unique list of all the data items in MyRange. Again, in this scenario, this
means we have a unique list of regions (East, North, South, West).
7. Step 7 works exclusively with the UList collection. This collection holds the unique list of
items we use as both the filter criteria for our AutoFilter and the Sheet names for our newly
created sheets. The macro starts looping through the list with the UListValue variable.
8. Each time we run this macro, a new sheet is added for each unique item in our target filter
field, with sheet names to match. If we run this macro more than one time, an error may be
thrown because we will be creating a sheet that already exists. To ensure this doesn’t hap-
pen, Step 8 deletes any sheet whose name matches the UListValue data item.
9. Step 9 uses the UListValue to filter the AutoFilter. We are dynamically passing the
UListValue as the Criteria for Field1:
MyRange.AutoFilter Field:=1, Criteria1:=UListValue
The field number here is very important! Because the Region field is the first field (see Figure
5-6), we are specifying Field 1. When you implement this macro in your environment, you
need to change the field number to match the field you need to parse.
10. Each AutoFilter object has a Range property. This Range property returns the rows to
which the AutoFilter applies, meaning it returns only the rows that are shown in the filtered
data set. Step 10 uses the Copy method to capture the newly filtered rows and paste the
rows to a new sheet. The macro then names the sheet to match UListValue.
Note that we are wrapping UListValue in the Left function. Specifically, we are telling
Excel to limit the name of the sheet to the left 31 characters in the UListValue. We do this
because the limit for sheet names is 31 characters. Anything longer than 31 characters
throws an error.
11. Step 11 loops back to get the next value from the UList collection.
12. The macro ends by jumping to the original AutoFiltered data and clearing all filters.
You may be wondering how to create a new workbook for each item in an AutoFilter.
This is a relatively easy change. Simply replace the code in Step 10 with this code.
‘Step 10: Copy the AutoFiltered Range to new Workbook
ActiveSheet.AutoFilter.Range.Copy
Workbooks.Add.Worksheets(1).Paste
Cells.EntireColumn.AutoFit
ActiveWorkbook.SaveAs _
Filename:=”C:\Temp\” & CStr(UListValue) & “.xlsx”
ActiveWorkbook.Close
C:Temp folder. If you like, you can change this to suit your needs.
How to use it
To implement this macro, you can copy and paste it into a standard module:
1. Activate the Visual Basic Editor by pressing ALT+F11 on your keyboard.
2. Right-click the project/workbook name in the Project window.
3. Choose Insert➜Module.
4. Type or paste the code.
