VBA to rename multiple worksheets

[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

[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

[JDeMaro22]

An absolute legend, thank you my friend