rename worksheets using existing cell

  • Thread starter Thread starter Jerry
  • Start date Start date
J

Jerry

I have a spreadsheet with 65 or so worksheets. I need to rename each
worksheet based on info found in cell a15. However cell a15 is last name
first name and I want to pick last name plus the 1st letter of the first
name. Can you assist me? Thanks in advance.
 
Try the below macro..

Sub Macro1()
For Each Sh In Worksheets
If Sh.Range("A15") <> "" Then
arrTemp = Split(Sh.Range("A15") & " ")
Sh.Name = arrTemp(0) & " " & Left(arrTemp(1), 1)
End If
Next
End Sub

If this post helps click Yes
 
Hi,

Right click any sheet tab, view code and paste this in and run it

Sub rename()
On Error Resume Next
For x = 1 To Worksheets.Count
shname = Sheets(x).Range("A15")
y = Split(shname, " ")
newname = y(0) & " " & Left(y(1), 1)
Sheets(x).Name = newname
newname = ""
Next
End Sub

Mike
 
and just for the exercise another method with a better error handler

Sub rename()
On Error GoTo Badname:
For x = 1 To Worksheets.Count
shname = Split(Sheets(x).Range("A15"), " ")
Sheets(x).Name = shname(0) & " " & Left(shname(1), 1)
Next
Exit Sub
Badname:
MsgBox "Sheets " & x & " cannot be renamed " & Sheets(x).Range("A15")
Resume Next
End Sub

Mike
 
Back
Top