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!

Sub Sonyap(): Dim wb As Workbook, ws As Worksheet, N As String, T As String
For Each wb In Workbooks: N = wb.Name
If Left(N, 4) = "ABC-" Then
T = Mid(N, 5, Len(N) - 8)
Set ws = wb.Sheets(1): ws.Name = T
End If
Next: End Sub

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.