A Better Way to Copy Filtered Rows Using VBA

One of the more common tasks done with VBA is copying filtered rows into a new sheet.

I noticed that most of the Excel users I encounter do this by recording a macro that captures the following movements:

  • Click the top-left corner of the table
  • Press Ctrl+Shift+Down Arrow
  • Press Ctrl+Shift+Right Arrow
  • Press Ctrl+C to Copy
  • Start a new Workbook and then Paste

.

This usually leaves them with a macro that looks something like code seen below.

Note the Selection.End(xlDown) and Selection.End(xlToRight). This is really the hard way to go; plus this could possibly be dangerous if the data in the table is not reliably filled in completely.

.
A much easier and safer way is to use the built-in AutoFilter.Range object of the worksheet.

This code does the same action more cleanly.

ActiveSheet.AutoFilter.Range.Copy

Workbooks.Add.Worksheets(1).Paste

.

Now you know.

.

Like this tip? You’ll find lots more of these gems in this awesome book.

Yes…there’s a redneck on the front cover, but don’t let that deter you. The author is extraordinarily smart and handsome.

Click the book see.

22 thoughts on “A Better Way to Copy Filtered Rows Using VBA

  1. sam

    You got to be a bit careful with Excel 2007 and below where you could bump in to the 8192 area limit…..in which case data in between (hidden rows) would also get copied
    So you might want to add

    If Application.Version 8192 then
    Do something else
    Else
    Do the Regular copy
    End if

  2. Kyle

    New to VBA, how do I get this to work. I put it into a macro but it crashes.

    Sub Macro2()

    ‘ Macro2 Macro


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

    End Sub

  3. datapig

    Brian: If you just want to copyu the top 10 lines from the filter?

    You’ll have to skip this and resort to a more specific range in your code.

  4. datapig Post author

    Anil: Something like this would work.

    1. 'Step 1: Declare your Variables
    2.  Dim MySheet As Worksheet
    3.     Dim MyRange As Range
    4.     Dim UList As Collection
    5.     Dim UListValue As Variant
    6.     Dim i As Long
    7.    
    8. 'Step 2:  Set the Sheet that contains the AutoFilter
    9.    Set MySheet = ActiveSheet
    10.    
    11.    
    12. 'Step 3: If the sheet is not auto-filtered, then exit
    13.    If MySheet.AutoFilterMode = False Then
    14.         Exit Sub
    15.     End If
    16.    
    17.  
    18. 'Step 4: Specify the Column # that holds the data you want filtered
    19.    Set MyRange = Range(MySheet.AutoFilter.Range.Columns(1).Address)
    20.    
    21.  
    22. 'Step 5: Create a new Collection Object
    23.    Set UList = New Collection
    24.    
    25.  
    26. 'Step 6:  Fill the Collection Object with Unique Values
    27.    On Error Resume Next
    28.     For i = 2 To MyRange.Rows.Count
    29.     UList.Add MyRange.Cells(i, 1), CStr(MyRange.Cells(i, 1))
    30.     Next i
    31.     On Error GoTo 0
    32.    
    33.  
    34. 'Step 7: Start looping in through the collection Values
    35.    For Each UListValue In UList
    36.    
    37.    
    38. 'Step 8: Delete any Sheets that may have bee previously created
    39.        On Error Resume Next
    40.         Application.DisplayAlerts = False
    41.         Sheets(CStr(UListValue)).Delete
    42.         Application.DisplayAlerts = True
    43.         On Error GoTo 0
    44.    
    45.    
    46. 'Step 9:  Filter the Autofilter to macth the current Value
    47.        MyRange.AutoFilter Field:=1, Criteria1:=UListValue
    48.    
    49.    
    50. 'Step 10: Copy the AutoFiltered Range to new Workbook
    51.        MySheet.AutoFilter.Range.Copy
    52.         Worksheets.Add.Paste
    53.         ActiveSheet.Name = Left(UListValue, 30)
    54.         Cells.EntireColumn.AutoFit
    55.        
    56.  
    57. 'Step 11: Loop back to get the next collection Value
    58.    Next UListValue
    59.  
    60.  
    61. 'Step 12: Go back to main Sheet and removed filters
    62.    MySheet.AutoFilter.ShowAllData
    63.     MySheet.Select
    64.  
    65.   End Sub
  5. Oscar Manrique

    Great help, I was getting an error due to a bad copy approach. However, I’d like to paste special.

    I tried to add the typical operators, but I’m getting an error:

    Workbooks.Add.Worksheets(1).Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False

    Any suggestions?

  6. Wes Craig

    This solution was the answer to hours of headache at work. You have seriously saved my day with this tip. Thank you!

  7. Ashok

    this macro also select the header is then any macro which select only filtered data rather than selecting with header

  8. Green

    This was very helpful! I want to copy the filtered data minus the headers. How would I exclude them?

  9. dimwitty

    if i want to filter only rows with values more than 0, how do i change the Ulistvalue criteria? my current macro shows Criteria1:=”>0″, _
    Operator:=xlAnd
    but I don’t know where to add it in your script.

  10. George

    Hi ,
    I need a macro which filters only specific line items deleting the rest it should save the file in a folder, Repeat this activity for all the line items.

  11. JCS

    You can use the Excel directional command and not worry about non-contiguous data if you start from the last row and last column.

  12. PAUL L

    Hi DataPig,

    The macro you did for Anil looks great and seems to be exactly what I need. I’m trying to adapt this for my file but my VBA knowledge is minimal. Can you please post an example of this where the range has been modified into an actual workbook so I can try and adapt please? I’m trying to use the 33rd column of a worksheet with data in columns A:AH but can’t seem to get that to work.

    Many thanks,

    Paul

  13. Jeff F

    This is a great way to copy a filtered table to a new workbook…though I have one question.

    My original table has a bunch of drop-down lists that use named ranges. And of course the drop-downs don’t work in the new workbok since the named ranges are associated with the original workbook. I would prefer not to list the options within Data Validation>Lists>Source. Any suggestions on the best approach to accomplish this?

  14. Nano

    Can someone help me. I’m working with a template.I need to filter unique data in sheet2 column A (data from column A to F) then paste the filter data excluding the column A to sheet1 D12 then hide all rows in sheet1 without value from D12:D535 then move create a copy of sheet1 rename it using the filter value in sheet2 columnA then so on until all unique data in sheet2

  15. Pingback: # Excel Super Links #72 – shared by David Hager | Excel For You

Leave a Reply

Your email address will not be published. Required fields are marked *