rename worksheets using existing cell
 

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.

rename worksheets using existing cell
 

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
---------------
Jacob Skaria


"Jerry" wrote:

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.

rename worksheets using existing cell
 

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

"Jerry" wrote:

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.

rename worksheets using existing cell
 

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

"Mike H" wrote:

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

"Jerry" wrote:

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.