Naming Multiple Worksheets

  • Thread starter Thread starter Michele
  • Start date Start date
M

Michele

I know you can rename worksheet tabs - one at a time, but is there a way to
rename several worksheets at one time. In a row of cells where you want to
have a range of dates inserted, all you have to do is drag the corner of the
first cell that has the date in there, and it will copy down the next date,
and so on. Can you do this in naming the tabs on the worksheets?

Thank you for your help.
 
Try the sub below (from Jim Cone) in a spare copy

In Sheet1,
Enter the sheetnames* in A1 down
Run the sub (adapt to suit)

*ensure that sheetnames in A1 down are valid ones,
eg do not contain prohibited char such as: / , ? , etc
and ensure there's no duplicate sheetnames in the list


Sub RenameShts()
'Jim Cone
Dim lngNum As Long
Dim lngTotal As Long

'Don't go past five sheets to the right (eg the "6" = 5+1, adapt to suit)
lngTotal = WorksheetFunction.Min(6, Sheets.Count)

'Rename sheets to something strange.
For lngNum = 2 To lngTotal
On Error Resume Next
Sheets(lngNum).Name = Chr$(lngNum + 128)
On Error GoTo 0
Next 'lngNum

'Rename sheets according to list.
For lngNum = 2 To lngTotal
On Error Resume Next
Sheets(lngNum).Name = Cells(lngNum - 1, 1).Value
On Error GoTo 0
Next 'lngNum
End Sub

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
 
I appreciate your response. However, I think this is a little above my head.
I don't know how to do subs. Is there anything simpler? I do appreciate
your response, though.
 
Okay. It's a spreadsheet that's done and changed every month. Oh well. I
would like to learn how to do all of what you're talking about some time.
Thanks for your reply.
 
Back
Top