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

"Rhoswen" wrote:

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.

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

"Mike H" wrote:

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" wrote:

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.

Glad I could help

On Thu, 5 Mar 2009 12:10:04 -0800, Rhoswen
wrote:

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

"Mike H" wrote:

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" wrote:

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.

How to rename all worksheets in one place:
http://www.youtube.com/watch?feature...&v=8RlXEKamKZ4