renaming multiple worksheets

Maggi: if you will be doing other stuff to each of the 40 workbooks, then adding the macro to each workbook is probably OK. I recommend that we expand this to automatically sequence through all workbooks, but let's get started with how to rename the sheets in a single workbook.. The macro has lots of comments, so you can follow what it is doing.

1. Make Sure You Can Run Macros
Excel has a security setting to ensure that malicious macros can not run without your knowledge. Some companies set up their computers with Macro security set to High. This is very safe and very conservative, but also means that you can not run macros casually. Open any workbook and navigate to Tools | Macros | Security (that’s the sequence for Excel2000). Check Medium. Once this is done, this security setting will be in force for all workbooks you open. You can reset the security setting at any time

2. Create the Macro
a. Copy the above code.
b. Open any workbook.
c. Press Alt + F11 to open the Visual Basic Editor (VBE).
d. In the left side window, hi-lite the target spreadsheet [it will likely be called VBAProject(name.xls) where name is the name of the spreadsheet]
e. Select an existing code module for the target worksheet; or from the f. Menu, choose Insert | Module.
f. Paste the code into the right-hand code window.
g. Close the VBE, save the file if desired.
h. See “Test The Code” below


Sub RenameSheets()
'
'****************************************************************************************
' Title RenameSheets
' Target Application: MS Excel
' Function; renames workbook sheets according to defined rules
' Limitations: presently limited to what is hardcoded in this proc, i.e,
' 3 original sheet names can re renamed. Total number of
' sheets does not matter.
'
' To expand or contract number of original/new sheet names:
' 1. reset dimension of OrigNames array and NewNames array
' 2. set value of Num to that same number
' 3. add or delete assignment statesments where specific names
' are assigned to OrigNames and NewNames
'
' Passed Values: NONE
' Public/Private Variables used: NONE
' VBA procedures called: NONE
' External Files Accessed: NONE
' Orig Date 21-Mar-2005
' Orig Author MWE
' HISTORY
'
'****************************************************************************************
'
'
Dim I As Integer, Num As Integer, Count As Integer
Dim OrigNames(3) As String, NewNames(3) As String
Dim WS As Worksheet
'
' define # of original sheet names and their corresponding new names
'
Num = 3
OrigNames(1) = "1000"
OrigNames(2) = "1100"
OrigNames(3) = "1200"
NewNames(1) = "Price"
NewNames(2) = "Product"
NewNames(3) = "Location"
'
' loop through all sheets;
' if sheet name is equal to one of the names in OrigNames array,
' rename that sheet to the corresponding new name and
' increment the counter by 1
'
Count = 0
For Each WS In ActiveWorkbook.Worksheets
For I = 1 To Num
If WS.Name = OrigNames(I) Then
Count = Count + 1
WS.Name = NewNames(I)
Exit For
End If
Next I
Next WS
'
' sheet examination is complete
' output message
'
MsgBox "Sheet examination and renaming is complete." + Chr(10) + _
"# of sheets examined = " + Str(ActiveWorkbook.Sheets.Count) + Chr(10) + _
"# sheets renamed = " + Str(Count), vbInformation

End Sub

3. Test The Code
Go to Tools | Macro | Macros and double-click on RenameSheets

I have attached a zipped workbook with 6 tabs; 3 of which are named with your original names and 3 of which have other names. The RenameSheets macro is installed as per the instructions above.

If each spreadsheet you receive is completely new and you will thus have to add the macro to each spreadsheet each time, that may be faster than your current approach, but it will eventually be a pain. If that is the case, we should create a Master spreadsheet that will sequence through the new spreadsheets and rename the tabs.. Not hard to do.

Thanks a lot. I am going to try it right now!

I am getting out of range error message.
I think I now where the problem is.
When one of my orginal sheet does not have date in it, it won't get exported so it cannot be renamed. Is there a solution for that?

Nevermind! Works Great! Thanks a million!

Originally Posted by maggi

Nevermind! Works Great! Thanks a million!

well, that's a relief

Let me know (send email) if you ever want to develop the master spreadsheet and really automate this.

Your e-mail is blocked Can you pm me or e-mail me
Thanks!

Originally Posted by maggi

I am getting out of range error message.
I think I now where the problem is.
When one of my orginal sheet does not have date in it, it won't get exported so it cannot be renamed. Is there a solution for that?

Maggi: the macro should run against ANY spreadsheet regardless of the actual tab names. If it encounters a tab name that is not in the array of OrigNames, that tab is bypassed. The MsgBox display at the end displays both the total number of sheets in the workbook and the number that were renamed. The first time you execute the macro in the example file I sent, it should rename 3 tabs. If you run the macro again, it will simply report that it examined 6 tabs and renamed 0

If the error message was "subscript out of range", then the problem is probably that you increased the value of Num without a corresponding increase in the dimension of OrigNames and NewNames.

Let's start with a few questions:

Did the original spreadsheet I provided run?

What opsys are you running? What version of Excel?

What is the exact wording of the error message?

When the error message occurs, you can click on Debug and see what line was executing when the error occured. What line was executing?

Did you expand the number of tab or sheet names? If so, did you change the dimension for both OrigNames and NewNames and reset Num to the new number?