Auto Filters - Copying and Pasting Data

  • Hi all,

    I have 2 Worksheets (Sheet1 and Sheet2) with 10 columns and thousands of rows.

    Based on a data item in column 7 I need to filter this data by this data item and then copy the entire rows matching this data item and paste onto a new worksheet (Which is named by this value). This will be repeated for 15 different data items. (Column 7 has maybe 100 different data items in it)

    I thought of three different ways I could do this.....

    1. I recorded a macro as below using Auto filter. I would need to have the criteria as a variable and also the rows that the auto filter has selected. But Im not familiar with the AutoFilter Object and I dont understand the help given by Microsoft.

    2. I have some code which deletes a row based on the criteria if the cell is equal to 4. Maybe I could adjust this to the 15 data items as the criteria. I dont mind deleting the data which doesnt match either of the 15 data items, in fact this would be better because this would reduce the size of my workbook...its pretty big already!

    3. I am being really dumb and there is a relly easy way that I have forgotten about!

    I think there is a brilliant example on this forum but its an old one and I cant read it due to all the &amps and the smily faces that have ended up in the code!!

    Anyway ...Im sure Ive rambled on enough and there will need to be more clarification....

    Thanks to anyone that can help!!! :)


  • Hi Dani

    For you first code, try this modified example that uses a variable as the AutoFilter Criteria. I have assumed that onece filtered you want to copy only the showing cells.

  • For the deletion of rows based on a criteria. The AutoFilter is good for this too. See example below based on your code;

    One of our resident gurus (XlDennis) has written some great stuff on AutoFilter in VBA, see

    The power of Autofilter in VBA 1

    The power of Autofilter in VBA 2

  • Hi Dave - -

    You wrote this:

    On Error Resume Next
    ActiveSheet.AutoFilterMode = False
    On Error GoTo 0

    Why do you trap for an error with setting AutoFilterMode to False? It never causes an error if the sheet is already not in AutoFilterMode; it simply sets the sheet out of AutoFilterMode if it exists and bypasses it if not. Am I missing something? Have you ever gotten an error or entered into Error mode by using that code line? I have not. Maybe you've seen an error with that codeline in previous versions? If so, do you recall which version and / or what circumstances? I've tried to create an error with that line and never could so I just use it without the trap but maybe I'm missing something.

    Tom Urtis

  • Hi Tom

    It's not really trapping the error, rather ingoring it. But you are right, there is no error generated if the filters are not on. I think 97 may have thrown and error and I haven't changed with the times. If Excel 97 doesn't, then I don't know why.

    I guess one never stops learning.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!