Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 ---- |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 ---- |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 ---- |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 ---- |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 ---- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Renaming Sheets | Excel Discussion (Misc queries) | |||
Renaming multiple sheets | Excel Worksheet Functions | |||
Renaming Sheets automatically | Excel Programming | |||
Renaming sheets | Excel Programming | |||
renaming sheets | Excel Programming |