Split workbook into multiple files based on column value

Important Notice

Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

  • So, the macro would:

    Reference the Names tab for the unique names

    Look to the 3 DATA tabs to filter the first unique name

    Highlight sheets 1 - 6, copy and paste values, and save down for that first unique name as a file Swanson, Ron.xlsx

    Then loop back to the Reference Names tab for the next name

    Look to the 3 DATA tabs to filter on the second unique name

    Highlight sheets 1 - 6 (that now have name 2 data referenced) save down for that second unique names as file Ludgate, April.xlsx


  • How do you determine which sheet to paste the data to ? There are three DATA tabs and 6 sheets to paste to.

    I don't see anything on any of the DATA tabs that would tell which sheet to paste to.

  • No need to paste any data...Sheets 1 - 6 reference the three DATA tabs. Once there is a filter applied for 1 Name, the references will update. Then I'd like that result to be saved as a new workbook like Swanson and Ludgate examples that are sheets 1 -6 only but copy paste special to remove the references.

    Even if a simpler version where we could have the macro reference the Name tab, filter the Name on Column A on Data1, Data2, Data3 and save down a copy of the workbook with that Name.xlsx and loop to the next name...that would be great too.

  • I think the "simpler" version would be perfect, and I know it's not simple :) that way I would have all the unique Names as their own files where the DATA 1, DATA 2, DATA 3 tabs are filtered just for their information

  • I've tweaked this a little bit, and maybe easier to understand.

    The process would be like this:

    Filter Data1 for the first Name on the Names tab

    Paste those values onto PASTEData1 tab

    Filter Data2 for the first Name on the Names tab

    Paste those values onto PASTEData2 tab

    Filter Data3 for the first Name on the Names tab

    Save down the workbook as Name.xlsx

    Repeat for Name 2 on the Names tab

Participate now!

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