AutoFilter Multiple Sheets, Copy, New Book, Paste to Multiple Sheets

  • A total beginner looking for some help with this - I need my code to filter through a specific column that is common across all 6 sheets, but may not be the same column number across all sheets. Hence the code will need some kind of unique identifier, probably the unique column name. I then need the autofilter to run through that column and copy the output for the 6 unique variables (which are also common across the 6 sheets I have in the original workbook), then save the output with the same sheet names as what they are named in the original wb into new workbooks. New workbooks to be named with the 6 unique variables in the autofilter column. My code now produces 6 new workbooks, saved as the 6 unique variables - which is great. But it is only doing this action for the 1st sheet and I need the same to be done for all 6 sheets in the original workbook, so that with each new workbook, I have my 6 other sheets populated with the filtered data. Hope this makes sense and would appreciate some support from someone who is better than this than I am! Sharing my code below:

  • Welcome to the Forum. Please read the Forum Rules to understand how the Forum works and why I have added Code Tags to your post

    All VBA code posted in the forum must be wrapped in code tags, which you omitted, including single-line code snippets.Be sure to use them in future posts.

    How to use code tags

    Note: no apostrophe in the tags, just used for demonstration here.


    your code goes between these tags


    Or, just highlight all of the code and press the <> in the post menu above button to add the code tags.


  • Try this, I haven't tested it

    Have you read the Forum Rules and understand about "Hijacking"? I am deleteing your post in the other person's Thread.

  • Tried it and got an error with this line -

    Set wb = Workbooks.Add

    Not sure if I am missing something, but your code doesn't have a 'paste' command. Though this is just a question on my mind, not an error as of yet.

    On a sidenote, VBA language is completely foreign to me and I'm seeing a couple of new instances with the code you just shared, like "For iX", "If iX =1". Tried exploring the different forums pages here but wasn't able to find a VBA 'dictionary' reference. Is there one for beginners like me here?

  • What error are you getting? There is nothing wrong with that line of code.

    IX is declared as an integer, For iX starts a Loop so that the code will work on each sheet of the workbook.

    The If statement checks if it is the first run of the Loop and adds a new workbook

    The way I have written the code does not need a Paste command

    This might help

    Keywords (Visual Basic for Applications)

  • Edit to the above comment - could not edit it with the edit option. I'm getting 'subscript out of range' error 9. My column title row start at a different row for each sheet, ranging from A6-A8. The column to be autofiltered and copied into the new wb, this column number is also not fixed. But the column name is called 'Division'. Perhaps we need a code to work around these 2 things?

  • Here it is. Essentially what is common across all sheets in this WB is the 'Division column'. This needs to be filtered to return results for each unique value, there are 5 - Abe, Sin, Cosin, Evb, Irel. I need 5 separate workbooks, named by each of these variables. The autofilter needs to repeat for all the other sheets in this WB so that in the new WB for Abe, I get 5 sheets that are filtered to only results for Abe, and the name of these sheets do not change. Remain as what is shown in the original WB.

  • First error occurs here, you haven't included the Function to create the unique list

    Call UniqueList(collection_UniqueList)

    I've used A8 to set the CurrentRegion, this should work for each sheet, unless there is no data in A8

    I get an error with this

    wb.SaveAs Filename:=Output_Folder_Path & "_" & collection_UniqueList.Item(instance) & ".xlsx", FileFormat:=51

    That's expected because I do not have the destination folder. Test this

  • I've removed the error handling in the second sub and the error is occurring in the collection sub to create the unique list

    There are two sheets where Column G is empty, I'm not sure if this is causing the error

Participate now!

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