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, 2022 in Others by Kithuzzz
• 38,010 points
629 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, 2022 by narikkadan
• 63,720 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, 2022 in Others by narikkadan
• 63,720 points
936 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, 2022 in Others by narikkadan
• 63,720 points
439 views
0 votes
1 answer

How to remove borders from cells in a range in Excel using VB.net?

range.Borders(Excel.XlBordersIndex.xlEdgeLeft).LineStyle = Excel.XlLineStyle.xlLineStyleNone range.Borders(Excel.XlBordersIndex.xlEdgeRight).LineStyle = Excel.XlLineStyle.xlLineStyleNone range.Borders(Excel.XlBordersIndex.xlEdgeTop).LineStyle = Excel.XlLineStyle.xlLineStyleNone range.Borders(Excel.XlBordersIndex.xlEdgeBottom).LineStyle ...READ MORE

answered Jan 5, 2023 in Others by narikkadan
• 63,720 points
1,431 views
0 votes
1 answer

Using Visual Basic to pull data from within a range to use in an Excel function

Use AVERAGEIFS instead of the full range. ...READ MORE

answered Jan 14, 2023 in Others by narikkadan
• 63,720 points
370 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, 2022 in Others by Edureka
• 13,670 points
821 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, 2022 in Others by narikkadan
• 63,720 points
1,500 views
0 votes
1 answer

Automate compound annual growth rate (CAGR) calculation

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

answered Oct 7, 2022 in Others by narikkadan
• 63,720 points
1,133 views
0 votes
1 answer

Insert pie chart in Excel macro function

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

answered Oct 14, 2022 in Others by narikkadan
• 63,720 points
984 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, 2022 in Others by narikkadan
• 63,720 points
883 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, 2022 in Others by narikkadan
• 63,720 points
1,670 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