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
----

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

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

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

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
----