Split 2 Tabs with Similar Names into Separate Files

[rsaran]

Hi,

I wanted some help in writing a code (I'm very limited in my knowledge of VBA). I have a workbook with 140 worksheets. The worksheets follow two different name formatting -

1. 70 sheets have a worksheet name which is the name of the person, i.e. Doe, Jane
2. The remaining 70 sheets are the name of the person appended with the word Summary i.e. Doe, Jane Summary

I need to break the excel file into 70 separate workbooks where each workbook is a combination of 2 two tabs, i.e. Doe, Jane and Doe, Jane Summary
I'm not sure if this is achievable and if it is how to go about it.

Please help!
Thank you so much!

[Artik]

e.g.

Please Login or Register  to view this content.

Artik

[rsaran]

This works perfectly!
Thank you so much!

[alansidman]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[rsaran]

One question, what if i have a couple of sheets that are "Rep Name Summar" because the rep name is too long so the entire word "Summary" is not being able to append in its entirety.

[Artik]

In such situations, does the sheet name always have the maximum number of letters (31)?

Artik

[davsth]

untested but perhaps something like the below if only the 2 situations can occur

Please Login or Register  to view this content.

[rsaran]

In such situations, does the sheet name always have the maximum number of letters (31)?

Artik

Yes, the sheet name will always have 31 characters. Because it will be the name of the person plus however many letters of the word summary can be fit in. In this case both the sheet names have Summar but it can be reduced to summa or su etc..

[davsth]

do any names exist like

Doe, Sally Anne
or
Wilson Jones, David?

[Artik]

I think that this macro should give yourself advice:

Please Login or Register  to view this content.

One condition. The " Summary" element must contain at least two characters - space and S.

Artik