rename worksheets using existing cell

[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.

 

[Jacob Skaria]

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

 

[Mike H]

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

 

[Mike H]

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