ExcelBanter - Worksheet Naming Options

Hi Comic Fly:

There are a number of ways of doing this one way is as follows:

In you list you have in say col A old you enter the old names and col B new
name and the list starts on row 2 (row one headings).

It also commens in col C.

The old names start in row 2 and you can do this quite easily =
"Sheet"&Row()-1 if the first sheet is called Sheet1 that you want to cfhange.

And then you run this macro. It also reports in col C if it has done it or
not.

Sub renamesheets()
Dim lRow As Long
Dim lLastRow As Long
On Error Resume Next
Application.ScreenUpdating = False
lRow = 2
With ActiveSheet
lLastRow = .Cells.Rows.Count
Do While (lRow < lLastRow And .Cells(lRow, 1) < "")
Worksheets(.Cells(lRow, 1).Value).Name _
= .Cells(lRow, 2).Value
If Err.Number < 0 Then
.Cells(lRow, 3) = "Error"
Err.Clear
Else
.Cells(lRow, 3) = "Done"
End If
lRow = lRow + 1
Loop
End With
Application.ScreenUpdating = True
End Sub

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.

"ComicFly" wrote:

I have a list of 40 names that I want to use to Name 40 worksheets in a
workbook. Is there a way to do this without having to type each name in each
worksheet Name area?