Rename Sheets VBA

Hello,
I have a workbook of about 200+ sheets.
I need to rename all sheets.

The new names are under a sheet called Names.
Column "A"  has old sheet names,  Column "C" has new sheets names.

Is there a macro that can help with this.
Example:
Old sheet name 200, NEW sheet name 9AM
Old sheet name 201, NEW sheet name 10AM

Any help is appreciated.
W.E.BAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
I have assumed your sheet names start from a1 and so on..You can use the following code to do what you are looking for...

Sub changenms()
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    Dim rng As Range, cell As Range
    Dim lrow As Long, ws As Worksheet

    lrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row

    Set rng = Range("a1:A" & lrow)

    For Each cell In rng

        If cell.Value <> "" And cell.Offset(0, 2).Value <> "" Then
            Set ws = Sheets(cell.Value)

            ws.Name = cell.Offset(0, 2).Value

        End If

    Next cell

    MsgBox "Done"

End Sub

Open in new window


Saurabh...
0
W.E.BAuthor Commented:
Hello  Saurabh,
I'm getting error 9
out of range  on line
Set ws = Sheets(cell.Value)

thanks
0
Saurabh Singh TeotiaCommented:
That means you don't have sheet with the name in the cell when you get this error what is cell.value you have? To check that just scroll on top of it and it will tell you..Can you check whether you have sheet with that name??

if it still doesn't work give me your sample file let me have a look over it...

Saurabh...
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

W.E.BAuthor Commented:
The only sheet that is not available is the one that holds the sheets old  namesa and new names.

Please see sample attached,

thanks,
Sample.xls
0
Saurabh Singh TeotiaCommented:
Use this...

Sub changenms()
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    Dim rng As Range, cell As Range
    Dim lrow As Long, ws As Worksheet

    lrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row

    Set rng = Range("a2:A" & lrow)

    For Each cell In rng

        If cell.Value <> "" And cell.Offset(0, 2).Value <> "" Then
            If IsNumeric(cell.Value) = True Then
                Set ws = Sheets("" & cell.Value & "")
            Else
                Set ws = Sheets(cell.Value)
            End If

            ws.Name = cell.Offset(0, 2).Value

        End If

    Next cell

    MsgBox "Done"

End Sub

Open in new window


Your workbook..

Saurabh...
Copy-of-Sample-1.xls
0
W.E.BAuthor Commented:
Perfect,
works great,
Thank you very much,

What if If I want to exclude a certain sheet,
0
Saurabh Singh TeotiaCommented:
Just don't mention their name in the your names sheet it won't change that..It will change only those names which are mentioned in your name sheet..Not all of them..

Saurabh...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
W.E.BAuthor Commented:
Thank you very much
Much appreciated.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.