Macro to Rename Worksheet Tab with Workbook File Name

Hi all - I'm looking for either a simple or more complex solution.

Simple: I'm looking for a macro to rename the Worksheet Tab in my workbook to the filename without the ".xls".

Complex: I have about 220 1-sheet workbooks that I need to consolidate into 22 separate workbooks with 10 worksheet tabs each.

For example, I have 10 separate workbooks with the file names as follows: ABC-Account1.xls, ABC-Account2.xls, ABC-Account3.xls....ABC-Account10.xls. Each of these workbooks consist of just 1 worksheet, tab named "Account Definite Production by "
I want to do the following:
- In each individual workbook, rename the Worksheet Tab to Account1, Account2, Account3....Account10 (remove the "ABC-"and ".xls")
- Create a NEW Workbook with the file name "ABC" and 10 worksheet tabs Account1, Account2, Account3....Account10

Any help will be greatly appreciated!
SP

Hi Sonyap,

Can you post a sample?

Hi Sonyap,

Or, a simpler fix, is just a macro to rename the worksheet tab as the filename without the prefix "ABC-" and ".xls" so that I can select/move the sheets into a new workbook.

This routine depends upon there being just one worksheet in each book!

Please Login or Register  to view this content.

Why not one workbook with 220 sheets??

Hi Sonyap,

You're welcome!

Consider using one book with the 220 sheets - you could, easily, set up a 221st sheet as an index and use a double click event to navigate between the index and the Account sheets.