Marco to rename worksheets from list

[Rhoswen]

I'm a VBA novice but am looking for a script that will rename all of the
worksheets in a workbook from a list based on its current name.

For example, the sheets may be named Sheet 1, Sheet 2 and Sheet 3
On Sheet 4 I have two columns of data: Col A with the current sheet names
and Col B with the new names.

Any suggestions? Thought this might exist somewhere already.

 

[Mike H]

Hi,

Right click any sheet tab, view code and paste this in and run it

Sub Sonic()
Dim MyRange As Range
LastRow = Sheets("Sheet4").Cells(Rows.Count, "A").End(xlUp).Row
Set MyRange = Sheets("Sheet4").Range("A1:B" & LastRow)
On Error Resume Next
For x = 1 To Worksheets.Count
Sheets(x).Name = WorksheetFunction.VLookup(Sheets(x).Name, MyRange, 2,
False)
Next
End Sub

Mike

 

[Rhoswen]

Works perfectly!!!! Thanks! You're my hero!

Hi,

Right click any sheet tab, view code and paste this in and run it

Sub Sonic()
Dim MyRange As Range
LastRow = Sheets("Sheet4").Cells(Rows.Count, "A").End(xlUp).Row
Set MyRange = Sheets("Sheet4").Range("A1:B" & LastRow)
On Error Resume Next
For x = 1 To Worksheets.Count
Sheets(x).Name = WorksheetFunction.VLookup(Sheets(x).Name, MyRange, 2,
False)
Next
End Sub

Mike

 

[EliMar]

How to rename all worksheets in one place: