Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Renaming sheets from a list
Hi guys,
In Sheet1, A1:B3, I have listed: DistA Dist1 DistB Dist2 DistC Dist3 where in A1:A3 are 3 existing sheetnames that I want to rename as in B1:B3, via a sub Thanks -- 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
Sub ReplaceSheetNames()
Dim sht As Worksheet Dim shtChange As String Dim i As Integer Set sht = Sheets("Sheet1") 'allow room to grow For i = 1 To sht.Cells(1).CurrentRegion.Rows.Count shtChange = sht.Cells(i, 1).Value 'get sheet name Sheets(shtChange).Name = sht.Cells(i, 2).Value 'change sheetname Next i End Sub Regards Andrew excelthoughts.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Renaming sheets from a list
Thanks, Andrew !
Runs smooth. Could I trouble you for a desired flexibility ? I'll like to be able to select the source range for the sub to execute, e.g.: A1:B3 in Sheet1 in the OP. This source range could be of any size in any sheet, but it will be a 2 column range. Thanks -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "excelthoughts" wrote in message oups.com... Sub ReplaceSheetNames() Dim sht As Worksheet Dim shtChange As String Dim i As Integer Set sht = Sheets("Sheet1") 'allow room to grow For i = 1 To sht.Cells(1).CurrentRegion.Rows.Count shtChange = sht.Cells(i, 1).Value 'get sheet name Sheets(shtChange).Name = sht.Cells(i, 2).Value 'change sheetname Next i End Sub Regards Andrew excelthoughts.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Renaming sheets from a list
Hi Max,
Could I trouble you for a desired flexibility ? I'll like to be able to select the source range for the sub to execute, e.g.: A1:B3 in Sheet1 in the OP. This source range could be of any size in any sheet, but it will be a 2 column range. Thanks Try: '------------------------------------------- Sub Tester1() Dim rCell As Range For Each rCell In Selection.Columns(1).Cells On Error GoTo ErrHandler If SheetExists(rCell.Value) Then Sheets(rCell.Value).Name = _ rCell(1, 2).Value Else MsgBox "Cell " & rCell.Address(0, 0, , 1) & _ " does not contain an existing sheet name" End If Continue: Next Exit Sub ErrHandler: MsgBox rCell(1, 2).Address(0, 0, , 1) & _ " Contains a duplicated or invalid sheet name" Resume Continue End Sub '------------------------------------------- Function SheetExists(sName As String, _ Optional ByVal wb As Workbook) As Boolean On Error Resume Next If wb Is Nothing Then Set wb = ActiveWorkbook SheetExists = CBool(Len(Sheets(sName).Name)) End Function '------------------------------------------- --- Regards, Norman "Max" wrote in message ... Thanks, Andrew ! Runs smooth. Could I trouble you for a desired flexibility ? I'll like to be able to select the source range for the sub to execute, e.g.: A1:B3 in Sheet1 in the OP. This source range could be of any size in any sheet, but it will be a 2 column range. Thanks -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "excelthoughts" wrote in message oups.com... Sub ReplaceSheetNames() Dim sht As Worksheet Dim shtChange As String Dim i As Integer Set sht = Sheets("Sheet1") 'allow room to grow For i = 1 To sht.Cells(1).CurrentRegion.Rows.Count shtChange = sht.Cells(i, 1).Value 'get sheet name Sheets(shtChange).Name = sht.Cells(i, 2).Value 'change sheetname Next i End Sub Regards Andrew excelthoughts.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Renaming sheets from a list
Marvellous, Norman !
Runs great ! Thanks. -- 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 all work-sheets at once | Excel Discussion (Misc queries) | |||
Renaming sheets from a list in a column | Excel Programming | |||
Renaming sheets | Excel Programming | |||
renaming sheets | Excel Programming |