Marco to rename worksheets from list
 

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.

Marco to rename worksheets from list
 

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.

Marco to rename worksheets from list
 

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.

Marco to rename worksheets from list
 

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.

Marco to rename worksheets from list
 

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