Split worksheet into multiple worksheets based on value in column A (BUT more complicated)

[carlito2002wgn]

Ok, I have a doozy here. Or, at least, I think it is complicated!

First, I have a macro that combines multiple workbooks from a folder on my desktop into a new single workbook. This works perfectly.

Second, I have a macro that names each sheet based on the value in cell A2. This works perfectly.

Here is my problem ... I thought that the value in cell A2 of each of my sheets was consistent down the column. It is not. Column A in all of my sheets contain Project numbers. I need one sheet per project number. Some sheets have only one project number, but others have multiple project numbers.

Is it possible, with code, that the sheets with multiple project numbers can be split into multiple sheets, and the sheets that already only have one project code can be ignored? Also, each sheet has a header row, so that would also need to carry over to new sheets.

If it is possible to do what I want, then I would need to run my macro for renaming all of the sheets afterward.

I have almost everything else figured out for this project!!

Thank you!

.
.
PS. The number of columns can vary from one sheet to another. That is why I do not try to load all of the original workbooks into a single sheet of a new workbook, and then split them out. That would be too easy!

PPS. Just to share, I do already have some code that splits one sheet into multiple sheets based on values in cells, but it works if starting with a single sheet, not multiple like in my request above. Anyway, I just thought I'd share that code in case it might give some value back to somebody:

Please Login or Register  to view this content.

Did not want to, but I am desperate: I sort of cross-posted at https://chandoo.org/forum/threads/sp...ontents.41060/ It's not exactly a cross-post, so I don't even know if I need to inform of it here, but I don't want my wrist slapped. Why is cross-posting even a problem? In life, don't we use all resources available to us? Seems silly, but whatever ... I want to be compliant.

[carlito2002wgn]

Upon thinking more about this problem, I thought of a possible alternate solution. It would not be ideal, but it would work in the long run:

Instead of splitting sheets with multiple project numbers into multiple sheets in the same workbook, would it be possible to parse all unique project numbers into unique workbooks, and save those to a specified folder? So, if sheet1 has one project number, and sheet2 has two project numbers, then the result would be three separate workbooks saved to the specified folder.

Note, some project numbers are common across two sheets. That means that the macro would need to be able to handle that by adding an index to the name when saving the file (or whatever solution somebody smarter than me can think of :-) )