Macro 59: Copy Filtered Rows to a New Workbook
Often, when you’re working with a set of data that is AutoFiltered, you want to extract the filtered
rows to a new workbook. Of course, you can manually copy the filtered rows, open a new workbook,
paste the rows, and then format the newly pasted data so that all the columns fit. But if you are doing
this frequently enough, you may want to have a macro to speed up the process.
How it works
This macro captures the AutoFilter range, opens a new workbook, then pastes the data.
Sub Macro59()
‘Step 1: Check for AutoFilter – Exit if none exists
If ActiveSheet.AutoFilterMode = False Then
Exit Sub
End If
‘Step 2: Copy the Autofiltered Range to new workbook
ActiveSheet.AutoFilter.Range.Copy
Workbooks.Add.Worksheets(1).Paste
‘Step 3: Size the columns to fit
Cells.EntireColumn.AutoFit
End Sub
1. Step 1 uses the AutoFilterMode property to check whether the sheet even has
AutoFilters applied. If not, we exit the procedure.
2. Each AutoFilter object has a Range property. This Range property obligingly returns
the rows to which the AutoFilter applies, meaning it returns only the rows that are shown in
the filtered data set. In Step 2, we use the Copy method to capture those rows, and then we
paste the rows to a new workbook. Note that we use Workbooks.Add.Worksheets(1).
This tells Excel to paste the data into the first sheet of the newly
created workbook.
3. Step 3 simply tells Excel to size the column widths to autofit the data we just pasted.
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.
