copy sheets to new workbook when sheet names are similar.

Hello,

I have a workbook that has 87 Sheets. The first sheet is ("All Detail"). Everything from that sheets gets splits out by salesman code, then each of that salesman sheets have a second sheet. For example my second sheet is STESU with all the information from STESU. Then there is a second sheet "STESU-Pivot". This is the same format for all the remaining sheets. I need a macro that will run through sheets 2 - 87 and take the salesman coded sheet along with the Pivot sheet associated to the salesman and put both sheets into it's own workbook, preferably with the Workbook named from the salesman code.
Then it would save all these workbooks into a particular folder. The folder is irrelevant for the macro so for simplicity the code can save it to the C drive.

There are plenty of macros out there about turning sheets into workbooks but i can't seem to figure out how to make a workbook from two sheets with the names being pulled in dynamically.

Also in the future there will be more than 87 sheets, so if the macro could be coded to accommodate more sheets in the future that would be great.

I have attached a sample that has 5 sheets but it gives you the idea of how my workbook looks.

Thanks for your help!

Using sheet index is a bit dicey, but if your file always follows the same order of starting with the second sheet, this should work

Please Login or Register  to view this content.

Genius... Thanks for your help!