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 <at>yahoo<dot>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

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 <at>yahoo<dot>com
----
"excelthoughts" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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" <[email protected]> wrote in message
news:uIfw9%23%[email protected]...
> 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 <at>yahoo<dot>com
> ----
> "excelthoughts" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>>
>
>

Marvellous, Norman !
Runs great ! Thanks.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----