How to Copy Data to Another Worksheet with Advanced Filter

Dear Microsoft, please fix the Advanced Filter in Excel. Copying filtered data to another worksheet should be simple but it’s not. Fixing this would help out many of your beginning and intermediate users and some of us old dogs who seemingly haven’t learned the trick.

The Problem with Advanced Filter in Excel

I was reading a post on the Contextures Blog, which indicated that you could use the Advanced Filter feature in Excel to copy filtered data to another worksheet. This feature has the potential to shorten some of my VBA code that essentially does the same thing. I mean, using Excel to do the work is simpler and usually faster than writing the code yourself.

So I created a simple worksheet with dummy data and tried out the Advanced Filter feature. In the picture below my List Range and Criteria Range are on Sheet1 and I’m just about to Copy to another location: Sheet2.

Advanced Filter Copy to Another Sheet

However, when I click the OK button the following message pops up.

Advanced Filter Warning

The Trick to Copy Filtered Data to Another Worksheet

My problem was that I didn’t know the secret. You have to initiate the Advanced Filter from the worksheet you are going to copy to. Duh, stupid me. So here’s how I defeated my nemesis, the Advanced Filter.

  1. Activate sheet 2 (where the data goes)
  2. Choose Data > Advanced Filter
  3. Select the List Range on sheet 1
  4. Select the Criteria range on sheet 1
  5. Select the radio button: Copy to another location
  6. Select the Copy to range on sheet 2
  7. Click OK

Here is the resulting data copied to Sheet 2.

Advanced Filter Results

There is a short video from Debra Dalgleish on how this is done. I’m too frustrated with Microsoft to pull off a video without disdain in my voice.

The problem I’m having is that this “workaround” has been required for quite some time, with no updating of the Excel software code by Microsoft. (The video link above is for Excel 2003.) And yet in the latest two versions of Excel, 2010 and 2011, we still have to do something, that seems to me to be, completely counter-intuitive and un-productive.

Advanced Filter with VBA

I turned on the macro recorder to document the VBA code required to execute, what I’m calling a “workaround.” I then copied the first macro, modified the code, and ran it again from sheet 1 and it worked just fine.

Advanced Filter VBA Code

The first macro: CopyToOtherSheet does two things. 1) Selects Sheet 2 because that is what Microsoft requires, then 2) uses the AdvancedFilter method of the Range property to FilterCopy the CriteriaRange to the CopyToRange, with no Unique records.

The second macro: FilterCopyToOtherSheets only does one thing. It does what Microsoft should have done long ago, and that’s to copy the filtered range (sheet 1) to the copy range (sheet 2) while your on sheet 1.

The only code I changed (highlighted in red) was to eliminate the selection of Sheet 2, and add Sheets(“Sheet2”). to the CopyToRange. You can run this macro from Sheet 1 and the Advanced Filter will copy the filtered data to Sheet 2 with no problems.

Of course, with static ranges, this is a very limited macro, but my point here is that the code change should be minimal and if Microsoft can spend several billion dollars to get Windows 7 into Nokia phones, this shouldn’t cost too much to fix.

12 thoughts on “How to Copy Data to Another Worksheet with Advanced Filter”

  1. Gregory,

    Great post. I’m running your macro, but unfortunately it only filters the data in “Sheet1.” It doesn’t actually copy over to “Sheet2.” Any ideas?

    Thanks,
    Amy

    • It depends on a number of things, so I’m not sure what to tell you. Perhaps I’ll add the worksheet as a download later today, which should work, then you could com are the differences.

      You might try and step through the macro (using F8 in the VBA Project) to see where it fails.

  2. This is great macro and it is very helpful for my work.
    However I have one question.

    When I change the criteria to “No” It copies only one cell. I thought it would overwrite previous data or something. But If I do a “Clear All” on Sheet2 it copies it without problem.

    Is there any possibility for this macro to overwrite previous data ?

    Kind regards, deyan

    • You can add a line of code to clear the contents of Sheet2 before any other code runs. Like this:

      Sheets(“Sheet2”).Range(“Extract”).CurrentRegion.ClearContents

      The Advanced Filter creates the “Extract” range name, in case you were wondering.

  3. I added the line and it works great. This was a really big help for me.

    Thank you for your time and for help.

    Best Regards, Deyan

  4. I have a worksheet, “sheet 1” that has 8 columns and ALOT of rows. I want to copy all the unique values based on information in one of the columns.

    I may have over 1000 rows. I want to base the “unique” factor on the info ini one of the columns… so say the columns is a bunhc of numbers 4, 3, 5, 3, 2, 1, 4 5, etc. based on this example, there would be 8 rows.. however, when copied to the new sheet, there would only be 5 rows (removing the duplicates).

    is this possible.

    • What you’re describing (at least as far as I understand it) seems possible, but not likely with that much data.

      A better way to show unique values is to make a copy of the worksheet by right-clicking the worksheet tab, clicking Move or Copy from the popup menu, then check the box to Create a Copy, and click OK. Then select the data, choose the Data tab on the Ribbon and click Remove Duplicates. Check the options you want on the Remove Duplicates dialog box and click OK.

      This is a quick and reliabe way to get the unique values from a data table.

  5. +1 very helpful post | the simple example spreadsheet let me try it process out before applying it to my large, complex sheet.

  6. very cool idea!!!
    you just wanna make a bit more dynamic like:
    Sheets(“Data”).Select
    ActiveSheet.Range(“A:F”).AutoFilter Field:=1, Criteria1:=”101″
    Range(“A2”).Select ‘ Start from A2 so the header is not copied
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.Copy
    Sheets(“101”).Select
    Range(“A2”).Select ‘ The cell where the data will start paste
    ActiveSheet.Paste

    • I have some more advanced ways to deal with this and generally you never want to select a worksheet when executing code. It slows the program down considerably.

      However, I get your meaning when you indicate the code is more dynamic by selecting a range that is not known, or changes. To shorten your code a bit, you can use:

      Range(“A2”).CurrentRegion.Offset(1, 0).Resize((Range(“A2”).CurrentRegion.Rows.Count – 1, Range(“A2”).CurrentRegion.Columns.Count).Copy

      which does the same thing as lines 3,4,5.

      Or you could set Range(“A2”).CurrentRegion as a Range variable and shorten this even further.

      Set rng as Range(“A2”).Current Region
      rng.Offset(1, 0).Resize((rng.Rows.Count – 1, rng.Columns.Count).Copy

  7. dear Friend
    this is great and worked fine with me. however, is there a way that refresh the sheet2. whenever I add something that to sheet one that satisfy the criteria, it automatically adds it to sheet2.

    or should I do the same steeps every time I want to Copy Filtered Data to Another Worksheet.

Comments are closed.