Microsoft Office Tutorials and References
In Depth Information
RENAME EACH WORKSHEET BASED ON ITS A1 VALUE
RENAME EACH WORKSHEET
BASED ON ITS A1 VALUE
Challenge: You have a workbook that has numerous worksheets. The title of
each worksheet is in cell A1. You want to name each worksheet based on its
cell A1 value.
Solution: You can quickly and automatically solve this problem by using a tiny
bit of VBA code. Here’s how:
Press Alt+F11.
Press Ctrl+G to open the immediate pane.
Type the following code and then press Enter:
For Each ws In Worksheets : ws.Name = Left(ws.Cells(1, 1).Value, 31) : Next
This is actually a three-line macro, with the lines separated with colons.
Additional Details: If any value in cell A1 contains more than 31 characters,
the name is shortened to 31 characters.
If any worksheet has an illegal character in cell A1, the macro stops with an
error. For worksheet names, the illegal characters are ’, *, /, :, ?, [, \, and ]. To
simply skip the worksheets that contain illegal characters, you can use this
macro:
Sub NoErrorNameThem()
On Error Resume Next
For Each ws In ThisWorkbook.Worksheets
ws.Name = Left(ws.Cells(1, 1).Value, 31)
Next
On Error GoTo 0
End Sub
Part
3
1.
2.
3.
To use alternate characters instead of the illegal characters, use this macro:
Sub ReplaceIllegalCharactersNameThem()
For Each ws In ThisWorkbook.Worksheets
NewName = ""
For i = 1 To Len(ws.Name)
ThisChar = Mid(ws.Name, i, 1)
Select Case ThisChar
Case "’", "*", "/", ":", "?", "[", "\", "]"
NewName = NewName & " "
Case Else
 
 
Search JabSto ::




Custom Search