Rename Excel worksheets from range

Question

First-time poster after years of lurking...

I have a little piece of code that I would want to run from a range of cells in the worksheet called Index in cells D5:D20 through worksheets 6 to the last worksheet. The names of the worksheets I want to use are in cells C5–C20, and in column D, these names are formatted so that they start with a number (1., 2., 3. and so on).

The renaming of Worksheet 6 goes smoothly. I keep getting the error "Run-time error '9': Subscript out of range" when it moves to worksheet 7.

This is the code I have written so far:

Sub RenameSheets()

Dim i As Integer
Dim j As Integer
Dim a As Integer

a = ThisWorkbook.Worksheets.Count

For i = 6 To a
 For j = 5 To 20
    
    If Worksheets(i).Name = Worksheets("Index").Cells(j, 3).Value Then
    Worksheets(i).Name = Worksheets("Index").Cells(j, 4).Value
    End If
    
 Next
Next

End Sub

What I would like it to do is Worksheets(6).Name = Worksheets("Index").Cells(5, 4).Value. then move to Worksheets(7).Name = Worksheets("Index").Cells(6, 4).Value

Answer 1

Rename Worksheets Using a List

• There are a few 'surprises lurking'. Some are covered deeper than others.
• The approach is quite different: it tries to rename whatever is in column C to whatever is in column D also monitoring the worksheet index.

I hope this helps you.