Rename Excel worksheets from range

0 votes

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

Nov 11 in Others by Kithuzzz
• 20,660 points
34 views

1 answer to this question.

0 votes

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. 
answered Nov 11 by narikkadan
• 37,660 points

Related Questions In Others

0 votes
1 answer

Excel VBA remove blank rows from specific range

I have tried to avoid .select  Option Explicit Sub CombineData() ...READ MORE

answered Oct 23 in Others by narikkadan
• 37,660 points
66 views
0 votes
1 answer

Copy Text from Range in Excel into Word Document

Here are some articles that may help: Control ...READ MORE

answered Nov 6 in Others by narikkadan
• 37,660 points
32 views
0 votes
1 answer

Change date format of cell in excel from dd.mm.yyyy to yyy/mm/dd ( excel version 2013 )

Hello :)   Excel’s Format Cells function can quickly ...READ MORE

answered Feb 9 in Others by gaurav
• 22,040 points
529 views
0 votes
1 answer

How to import data from a HTML table on a website to excel?

Hello  To import any HTML file in excel there ...READ MORE

answered Feb 10 in Others by gaurav
• 22,040 points
306 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17 in Others by Edureka
• 13,640 points
109 views
0 votes
1 answer

Calculate monthly average from daily data without PivotTable

Assuming you have the months in column D enter ...READ MORE

answered Oct 3 in Others by narikkadan
• 37,660 points
69 views
0 votes
1 answer

Automate compound annual growth rate (CAGR) calculation

The following PowerPivot DAX formulas worked for ...READ MORE

answered Oct 7 in Others by narikkadan
• 37,660 points
89 views
0 votes
1 answer

Insert pie chart in Excel macro function

Think about arranging your data in a ...READ MORE

answered Oct 14 in Others by narikkadan
• 37,660 points
45 views
0 votes
1 answer

Extract unique value from the range in Excel

The portability of spreadsheet functions like UNIQUE() ...READ MORE

answered Sep 23 in Others by narikkadan
• 37,660 points
82 views
0 votes
1 answer

Remove formulas from all worksheets in Excel using VBA

Try this : Option Explicit Sub test1() ...READ MORE

answered Oct 3 in Others by narikkadan
• 37,660 points
93 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP