VBA to rename multiple worksheets

JDeMaro22
Lounger
Posts: 35
Joined: 16 Oct 2021, 16:22

VBA to rename multiple worksheets

Post by JDeMaro22 »

Hello,

I have a workbook pulled from a database that has 126 worksheets all of which need to be renamed to their proper region. The region names are located in the Table of Contents tab and I was hoping for a macro that can use the specific cells to rename each. If you take a look at the screenshot you'll see the worksheet region names start at C8-C132.

Please let me know if this is achievable, if not I'll start grinding away.

Thanks you,

Joshua
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 74155
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: VBA to rename multiple worksheets

Post by HansV »

Try this:

Code: Select all

Sub RenameSheets()
    Dim ws As Worksheet
    Dim r As Long
    Set ws = Worksheets("Table of Contents")
    On Error GoTo ErrHandler
    For r = 8 To 132
        Worksheets(ws.Range("A" & r).Value).Name = Left(ws.Range("C" & r).Value, 31)
    Next r
    Exit Sub
ErrHandler:
    MsgBox "Could not rename '" & ws.Range("A" & r).Value & "'", vbExclamation
    Resume Next
End Sub
Regards,
Hans

JDeMaro22
Lounger
Posts: 35
Joined: 16 Oct 2021, 16:22

Re: VBA to rename multiple worksheets

Post by JDeMaro22 »

An absolute legend, thank you my friend