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
VBA to rename multiple worksheets
-
- StarLounger
- Posts: 94
- Joined: 16 Oct 2021, 16:22
VBA to rename multiple worksheets
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: VBA to rename multiple worksheets
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
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 94
- Joined: 16 Oct 2021, 16:22
Re: VBA to rename multiple worksheets
An absolute legend, thank you my friend