Renaming Sheets

A

Accor

Is there a short way to rename sheet1, sheet2, sheet3....sheet20 without
having to select one by one.

Many thanks
 
J

jamesgreen55

Is there a short way to rename sheet1, sheet2, sheet3....sheet20 without
having to select one by one.

Many thanks  
Click to expand...

There are many ways but my first question would be what do you want to
name the worksheets? Do you have a list somewhere with the desired
names or is it one name that is incremented numerically?

if it's the former see below - NOTE:Cut from the archives;

<snip1>
"The following example has the sheet names in cells A2:A8
inclusive. This will create sheets with the names as per
the cells. It should be easy enough to modify. For
example, once you load the array, you can clean out the
names from the source sheet. If you want the sheets in a
different order, reverse the array selection, or put them
in the source sheet in the reverse order.

Tony

Sub bbb()
Dim arr As Variant
arr = Range("a4:a8").Value
For i = LBound(arr) To UBound(arr)
Set NewSheet = Sheets.Add
NewSheet.Name = arr(i, 1)
Next i

End Sub
</snip1>
 
G

Gord Dibben

James

I believe Accor wants to rename existing sheets, not create new ones.


Gord Dibben MS Excel MVP
 
A

Accor

Thanks, but It isn't quite what I need. Firstly, I have a Workbook with 20
Sheets, which contains data . I don't want to add extra sheets and change
names. I just like to change their current names ( based on a list on Sheet 1
(a1:a20))

Any further help would be greatly appreciated
 
A

Accor

Excellent!

Below did the trick for me..

Thanks guys

'name sheets with list of unique names in A1:A20 on first sheet
On Error Resume Next
For i = 1 To Worksheets.Count
Sheets(i).Name = Sheets(1).Cells(i, 1).Value
Next i
End Sub
 
G

Gord Dibben

Good to hear.

Gord

Excellent!

Below did the trick for me..

Thanks guys

'name sheets with list of unique names in A1:A20 on first sheet
On Error Resume Next
For i = 1 To Worksheets.Count
Sheets(i).Name = Sheets(1).Cells(i, 1).Value
Next i
End Sub
Click to expand...
 
Top