Marco to rename worksheets from list

  • Thread starter Thread starter Rhoswen
  • Start date Start date
R

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.
 
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
 
Works perfectly!!!! Thanks! You're my hero!

Mike H said:
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
Click to expand...
 
Back
Top