Renaming sheets from a list in a column
 

Hi guys,

I've got a list of names in say A1:A50 in Sheet1. I want to rename the 50
immediate* sheets to the right of Sheet1 with the 50 names in the list, in a
"transpose-like" fashion, i.e. the name in A1 replaces the name of the sheet
just to the right of Sheet1, that in A2 goes into naming the sheet 2 tabs to
the right of Sheet1, and so on. I'll like the sub to return me to Sheet1 at
the end of the task. Thanks.

*there may more than 50 sheets to the right of Sheet1. But if there's less,
just rename from A1 down until the last sheet to the right.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Renaming sheets from a list in a column
 

And should there be any invalid or duplicate names in A1:A50 encountered
along the way (although the list will be checked carefully before the run),
just skip over these in the sheet renaming process.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Renaming sheets from a list in a column
 

Hello Max,
'-----------------------
Sub ChewingGum()
Dim objSht As Object
Dim lngNum As Long
lngNum = 1
For Each objSht In Sheets
On Error Resume Next
objSht.Name = Cells(lngNum, 1).Value
On Error GoTo 0
lngNum = lngNum + 1
Next 'objSht
Set objSht = Nothing
End Sub
'-------------------------
Regards,
Jim cone
San Francisco, USA


"Max" wrote in message
...
And should there be any invalid or duplicate names in A1:A50 encountered
along the way (although the list will be checked carefully before the run),
just skip over these in the sheet renaming process.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Renaming sheets from a list in a column
 

Hello Again Max,

Reread your post.
Better try this code instead of what I posted earlier.
It leaves the first sheet alone and gives you a better
chance of actually changing the names...
'-------------------------
Sub BubbleGum()
Dim lngNum As Long

'Rename sheets to something strange.
For lngNum = 2 To Sheets.Count
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 Sheets.Count
On Error Resume Next
Sheets(lngNum).Name = Cells(lngNum - 1, 1).Value
On Error GoTo 0
Next 'lngNum
End Sub
'-------------------------


Regards,
Jim Cone
San Francisco, USA

Renaming sheets from a list in a column
 

Many thanks, Jim !

Your Sub BubbleGum() was marvellous.

But as chewing gum and bubble gum are banned over here <g think I'll need a
slight tweak. Found that the sub renamed even those sheets which were
beyond the immediate 50 to the right of Sheet1 (assuming the list in
A1:A50**) with "funny looking" characters. And it did this renaming even
when I tried to hide away all the sheets which were beyond the immediate 50,
hoping this would prevent it.

How could this tweak be effected ? I'm prepared to select the list A1:A50
in Sheet1 via say, an inputbox if this makes it easier to accomplish the
control on the sub's execution. Thanks.

**below A50 would be just an empty cell to mark the end of the list in
A1:A50
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Renaming sheets from a list in a column
 

Max,
"Slight tweak" follows...
Jim Cone

'-------------------------
Sub SomethingHealthy()
Dim lngNum As Long
Dim lngTotal As Long

'Don't go past fifty sheets
lngTotal = WorksheetFunction.Min(50, 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" wrote in message
...
Many thanks, Jim !
Your Sub BubbleGum() was marvellous.
But as chewing gum and bubble gum are banned over here <g think I'll need a
slight tweak. Found that the sub renamed even those sheets which were
beyond the immediate 50 to the right of Sheet1 (assuming the list in
A1:A50**) with "funny looking" characters. And it did this renaming even
when I tried to hide away all the sheets which were beyond the immediate 50,
hoping this would prevent it.

How could this tweak be effected ? I'm prepared to select the list A1:A50
in Sheet1 via say, an inputbox if this makes it easier to accomplish the
control on the sub's execution. Thanks.
**below A50 would be just an empty cell to mark the end of the list in
A1:A50
Rgds
Max
xl 97
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom

Renaming sheets from a list in a column
 

Very good, Jim. Thanks !

Had to adjust the number up by one to 51, though, in the line
lngTotal = WorksheetFunction.Min(50, Sheets.Count)

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----