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

Many thanks

On Aug 15, 7:01*am, Accor wrote:
Is there a short way to rename sheet1, sheet2, sheet3....sheet20 without
having to select one by one.

Many thanks *

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

You had several responses to this same question yesrerday.

Do you not see them?

Here is URL to see them.

http://tinyurl.com/573w52


Gord Dibben MS Excel MVP


On Fri, 15 Aug 2008 04:01:01 -0700, Accor
wrote:

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

Many thanks

James

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


Gord Dibben MS Excel MVP

On Fri, 15 Aug 2008 05:33:04 -0700 (PDT), wrote:

On Aug 15, 7:01*am, Accor wrote:
Is there a short way to rename sheet1, sheet2, sheet3....sheet20 without
having to select one by one.

Many thanks *

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

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

" wrote:

On Aug 15, 7:01 am, Accor wrote:
Is there a short way to rename sheet1, sheet2, sheet3....sheet20 without
having to select one by one.

Many thanks

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

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

"Gord Dibben" wrote:

You had several responses to this same question yesrerday.

Do you not see them?

Here is URL to see them.

http://tinyurl.com/573w52


Gord Dibben MS Excel MVP


On Fri, 15 Aug 2008 04:01:01 -0700, Accor
wrote:

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

Many thanks

Good to hear.

Gord

On Fri, 15 Aug 2008 08:00:01 -0700, Accor
wrote:

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

"Gord Dibben" wrote:

You had several responses to this same question yesrerday.

Do you not see them?

Here is URL to see them.

http://tinyurl.com/573w52


Gord Dibben MS Excel MVP


On Fri, 15 Aug 2008 04:01:01 -0700, Accor
wrote:

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

Many thanks