Macro - VBA for selecting sheet based on cell value and moving them to a new workbook

[HelloVBA]

Hello all VBA pros out there. I've never realised splitting a workbook up into multiple workbooks can be so difficult and I'm at my wits end.

Would actually like to find out if the below is actually humanely possible.

What I'm trying to do:
- Split a workbook with multiple tabs up based on the criteria that cell B1 is the same
- Note that B1 is NOT the same value in all sheets and I would like a macro to be generated based on all the variables. For example, B1 = A in one new workbook (contains multiple sheets with B1 = A on original workbook. Then B1 = B in another new workbook (once again contains multiple sheets with B1 = B) and so on and so forth.

How I'm trying to go about it
- Write a VBA code that selects all sheets that B1 = A
- Move these selected worksheets to a new workbook and make sure that it's "paste values". Original file unaffected.
- Rinse and Repeat for B1 = B, B1 = C etc.

Any simpler ideas is definitely welcome as well.

I've had done some research and found 2 codes that might be useful, however it doesn't do exactly what I require.
[URL unfurl="true"]https://www.tek-tips.com/viewthread.cfm?qid=849130[/url]
[URL unfurl="true"]https://stackoverflow.com/questions/43763372/edit-vba-to-paste-multiple-sheets-as-values-into-new-workbook[/url]

Thanks very much in advance.

 

[SkipVought]

Hi,

I am always skeptical of the reasons why one might want to chop up data into different files, different sheets, different tables. It makes accessing and analyzing the data, much MUCH, MUCH more difficult!

But I see, as I read, that the original is left unchanged. So I'll take a stab at it in the next post.

Your explanation is not clear. The value in B1 is supposed to be the new file name. Are you saying that SOME the values in B1 on each sheet are identical and if so should all be put into the new workbook by the same name and if different, then in new workbook(s) corresponding to the B1 value?

It would help if you were to upload a sample workbook that illustrates your situation.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[HelloVBA]

Hello Skip!

Thanks so much for taking interest in this. Here is a simplified sample of the file attached.

I really wish I don't need this code too but it's mainly for confidentiality purposes and easy dissemination.

//Your explanation is not clear. The value in B1 is supposed to be the new file name. Are you saying that SOME the values in B1 on each sheet are identical and if so should all be put into the new workbook by the same name and if different, then in new workbook(s) corresponding to the B1 value?//

On this, is it possible to have the new file name as "Original file name - Cell B1"?
Yes, 4 sheets can have the same B1 - these 4 sheets in one new excel workbook, then 2 sheets the same B1 - these 2 sheets in a new excel workbook, etc.

Would it cause a problem if I have other sheets in the workbook as well that doesn't require it to be split? I don't mind if the macro splits it to a new workbook, I can just delete the additional file created.

Let me know your thoughts once you have a go!