Split workbook into multiple files based on column value

  • Hi everyone, relatively new to VBA so appreciate any guidance with this problem :) I've attached a simple workbook.

    I'd like to split my master Excel workbook into multiple workbooks based on name criteria. Here is the process:

    Filter column A on the 3 data tabs for each name referenced on the Names tab. So for the first name on the Names tab, 'Swanson, Ron', filter that name on Data1, Data2, Data3 tabs.

    Highlight the first 6 tabs (Sheet1 - Sheet6)

    Copy paste values

    Save down just those sheets as separate workbook named Swanson,Ron.xlsx

    Repeat for the next name

    Here is some code I have tried that I found on a separate thread, this will only filter one tab and I'm a bit confused by it. Sheets 1 - 6 do not have any data, but this is just for simplicity, in reality this has automated formatting and formulas that are generated off results of my data tabs.



    Thank you so much for any help here

  • Why highlight / select the six blank sheets for pasting to ? You have three names attached to three titles.

    Also ... why filter on the employee title ? Would there be a problem if you filtered on the employee name / Col A ?

  • Hi, just made some quick edits to the post...sorry about that.

    Would be Column A and Sheets 1 - 6 do not have any data, but this is just for simplicity, in reality this has automated formatting and formulas that are generated off results from my data tabs. I also have many more names than just 3, have about 200.

  • Ok ... sounds good.

    Will you always have DATA1, DATA2 & DATA3 tabs ? My thought is to first consolidate the data from all three tabs to a single sheet,

    then filter the data. If doing it this way ... do you perceive an issue ?

  • Yes will always have those 3 data tabs. Unfortunately, the actual structure of those 3 tabs is more complicated so I won't be able to consolidate to a single sheet...but column A will always be the reference point for each.

  • Hello,

    Based on Advanced Filter, you could take a look at Excel Explosion from Data Pig ...

    where you can either create worksheets or workbooks ...

    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Need to ask another question ...

    Data1 layout is:

    A B C D

    Name Title Hours Metric

    Data2 layout is :

    A B C D E F

    Name Title Hours Metric1 Metric2 Metric3

    Data2 layout is:

    A B C

    Name Title Hours

    Will these three tabs always have the same layout ? If they change ... how do they change ?

  • No problem, ask any questions! I see my attempt to make the workbook simple took away a bit from my actual structure... Data1 structure is A:M, Data2 is A:AD, and Data3 is A:E. The header labels will likely change over time and maybe I add another column to the end, but I can find my way around that type of edit.

  • Hmmm ..... if the sheets layout vary from one to another ... if the future brings other changes .... creating a formula or macro

    that will accomplish your goal is not possible.

    To attain the goal .... your workbook will need to have consistency with the DATA tabs and the NAMES tab.

    Specifically with the DATA tabs, the tabs needs to be arranged identical to DATA2 , even though some of the columns may not have entries

    depending on the tab :

    Name Title Hours Metric1 Metric2 Metric3

    Also, there needs to be a NAMES tab, with the names listed in Col A as presently show.

    Is this possible ?

  • Ok, i see....the structure will be the same where we can always filter on Column A, but if everything else needs to be consistent as well I won't alter it. As you suggest, we can tack on those tabs even if there is no data underneath so DATA tabs match.

    There does not need to be a NAMES tab, I thought that might be easier but not needed.

  • Comments:

    There are different ways to accomplish your goal. Presented here is one.

    To provide an answer quickly, I chose to work with macros saved in my "toolbox". This is not a reflection

    on the code you posted.

    This code will function as needed so long as it is used "as is". If the sheet names / locations / index positions are changed, the code

    will not function as needed.

  • Just realized I left out one line of code ...

    Updated code :

  • Thank you so much for this. However, after filtering column A on each DATA tab for the name, I'm looking to have all 6 sheets saved together. So filter on the name on the Data tabs, highlight the 6 sheets, copy paste values, and save as its own Excel workbook. We would have a Swanson, Ron.xlsx with the 6 sheets, Ludgate, April.xlsx with the 6 sheets and so on. Not the 6 saved out individually... Is it possible to do so?

  • Sorry ... I don't understand your stated goal.

    Create a sample workbook with the data separated as you indicated in your last post so it can be compared with the data in its original

    form / location.

  • No problem, it's confusing and appreciate your help figuring it out. This macro is very close.

    Attached 'parksdata' is the master workbook. My first 6 sheets will reference the three data tabs in some way, so when it is filtered for just 1 Name at a time, they will only include data summaries for that Name. I made very simple references on the 6 sheets to demonstrate. That's why I'd like them to be copy + paste special because when deleting out the DATA and reference tabs will cause an error when just Sheet1-Sheet6 are saved....

    Looking at Swanson, Ron.xlsx and Ludgate, April.xlsx this would be final output. Does this help?

  • Close...the 3 data tabs are my data dump tabs only that I use for reference for sheets 1 - 6. Those summaries on sheets 1 - 6 are what I'd like as the final output and are based on what has been filtered from Column A on my DATA tabs.

Participate now!

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