Split .xlsm workbook into multiple xlsm workbooks w/ 1 tab each

Hi all, looked all over the internet for the answer to my problem. I am trying to use a macro to split out 14 tabs each saved as its own file (named according to the tab name). The tabs themselves have macros that need to be preserved. I have the code below which almost does the trick (brings the macros over, saves as separate files, and deletes extra tabs) however it only deletes the tabs previous to the one being saved. So if tabs are A, B, C, then the first file is named 'A' (with A, B, C); second file is named 'B' (with B, C), and 3rd is named 'C' (with C only). Thanks in advance.

Please Login or Register  to view this content.

I think the problem is with the line of code: sh.SaveAs ActiveWorkbook.Path & "/" & sh.Name

It is almost there!

Try changing it to ActiveWorkbook.SaveAs iPath & sh.name


When you reply please make it clear WHO you are responding to by mentioning their name.
If this has been of assistance, please advise. A little thanks goes a long way.
- Please click on the *Add Reputation button at the bottom of helpful responses.
Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

OK I am embarrassed I actually posted the wrong code. The code I originally posted returns separate books which pop up on the screen. Each has only 1 sheet. However, using the code suggested by David A Coop, (which does work generally speaking), they save as .xlsx files and lose their macros. The code I meant to post was:

Please Login or Register  to view this content.

This code is the one that actually does the save as an xlsm file but doesn't not give the desired 1 tab per file. Instead does the cascading tab deletions. I think with the first code I posted the "Copy" function is causing it to lose the macros. Please let me know. Thanks!

I should add that I did try the code suggested by AlphaFrog. It return a compile error in hidden module: ThisWorkbook. I am trying to look online about what that means. Thanks everyone!

I've been doing some research and it seems there isn't an easy way to solve this problem due to the macros in each of the tabs. I can successfully parse out
1) 1 wb -> 14 wbs w/ 1 tab each but no macros, or
2) 1 wb -> 14 wbs with all the tabs (14) and all the macros, or
3) can create a loop to delete each successive tab and save each file (so like I first described file A has A, B, C; file B has B, C; file C has just C) with the macros.

But I can't seem to get all the pieces to align so I can go from 1 wb to 14 wbs with 1 tab each and all macros preserved.

Hi JenBR,

Here is a modification of your original post. You said that my suggestion almost worked!

Please Login or Register  to view this content.

This code works for me now. I shown the extra code in red!

I wasn't sure what you were trying to achieve with your second post, and the deletion of sheets etc.

Regards,

David


When you reply please make it clear WHO you are responding to by mentioning their name.
If this has been of assistance, please advise. A little thanks goes a long way.
- Please click on the *Add Reputation button at the bottom of helpful responses.
Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

Dear David, this works now to create 1 file for each tab. The file is macro-enabled and saves to the correct location. It does not however preserve the macros. I guess I should follow what Alpha Frog says and see if that works.

David, I did figure out that if I put the macros into each tab's sheet module (instead of the ThisWorksheet module) the code does get into the new files that are created. I just have to manually go in and assign the macro to the button. Not sure why it does this .

Hi Jen,

I tested this and can see what you mean.

I recorded a simple macro when I fixed the link from the original file to the newly created file. It came back like this:

Please Login or Register  to view this content.

Perhaps you can work these couple of lines of code into your loop so that it re-establishes the link to the new file instead of the old.

Regards,

David



When you reply please make it clear WHO you are responding to by mentioning their name.
If this has been of assistance, please advise. A little thanks goes a long way.
- Please click on the *Add Reputation button at the bottom of helpful responses.
Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

Thanks David, and others. I have decided to use an Add-In instead. That solves the problem at least for now. I never was able to get my macro to automatically sign the new buttons (they kept referring back to the original file).