Running sheets VBA

Question

I have created many sheets (around 30) in a same workbook. I'm trying to program with VBA where a value will appear in the designated cell of the multiples all at one, but there is an issue "Run time error 9, Subscript out of range".

Sub Invoice()
     For i = 5 To 11
       With Sheets("Sheet" & i)
           .Range("J2") = "a"

       End With
      Next i

End Sub

As shown in the code, value "a" has to appear in cell J2 on every sheet from sheet5 to sheet11. Apparently, there is something, but I just don't know what has gone wrong?

Below, I tried testing with just 3 sheets and still it gives the same error. As you can see in the picture that the name of each sheet is spelled correctly? I tried rewriting the names, but it doesnt work as well...

It means that one of the sheet's name is not `"Sheet" & i` because you misspelled it or there is a white space at the end. `Sheet5` and `Sheet5 ` are different names because there is a blank at the end of `Sheet5 `. See [here](https://excelmacromastery.com/excel-vba-worksheet/#Subscript_Out_of_Range) for further possible reasons. — Storax, Oct 13 '19 at 15:14
Plase have a look [here](https://excelmacromastery.com/excel-vba-worksheet/#Using_the_Code_Name_of_a_Worksheet). Your codename is like `Sheet1`, `Sheet2` etc., and your worksheet name is `030`, `031` etc. — Storax, Oct 13 '19 at 17:10
On a side note, please consider https://stackoverflow.com/q/218623/11683. — GSerg, Oct 13 '19 at 17:30

Storax · Accepted Answer · 2019-10-13T17:35:55.620

Based on the picture you added to your post I guess you want to refer to the code name of the sheet and not to the worksheet name.

Please look here at macromastery for an explanation of the difference.

Sub Invoice()

    Dim wks As Worksheet
    Dim i As Long

    For Each wks In ThisWorkbook.Worksheets
        For i = 5 To 11
            If wks.CodeName = "Sheet" & i Then
                wks.Range("J2") = "c"
            End If
        Next i
    Next wks

End Sub

As an additonal note Run-time Error 9 Subscript out of Range when you use Worksheets in such cases is always caused by one of the following reason:

The worksheet name given to Worksheets is spelled incorrectly.
The name of the worksheet has changed.
The worksheet was deleted.
The index was to large e.g. You used Worksheets(5) but there are only four worksheets
The wrong workbook is being used e.g. Workbooks(“book1.xlsx”).Worksheets(“Sheet1”) instead of
Workbooks(“book3.xlsx”).Worksheets(“Sheet1”).

score 0 · Answer 2 · answered Oct 13 '19 at 15:32

0

"Run time error 9, Subscript out of range" in this context means that you were trying to access an item in a collection (WorkSheets collection) by providing a non-existent index.

The Sheets(index) accessor accepts 2 types of indexes:

A number, corresponding the order on which they appear in your workbook (1 based), or
a string, corresponding the exact name of the worksheet. So, in order to avoid error 9, make sure you provide a valid index value, either by passing in a number:

Private Sub Invoice()
    For i = 5 To 11
        Sheets(i).Range("J2") = "a"
    Next i
End Sub

or by specifying exactly the names of the worksheets you want to apply changes to.

answered Oct 13 '19 at 15:32

Mrblackey

94
4

This is a cool suggestion. It avoids spelling errors in the sheet names. – Gary's Student Oct 13 '19 at 15:35
1

How do you know `Sheet5` to `Sheet11` are at index positions 5 to 11? – chris neilsen Oct 13 '19 at 15:45
How do you know Sheet5 to Sheet11 even exist? It seems more likely to me that they don't. I mean, at least not by these names. – Mrblackey Oct 13 '19 at 15:47
Oh, I might have misunderstood your question. I don't know that for sure, this is exactly why I bothered to specify what the integer indexing is based on. – Mrblackey Oct 13 '19 at 15:55
@Chris Neilsen I uploaded a picture of the sheets above. As you can the name of Sheet 5 is "Sheet5", Sheet 6 is "Sheet6"...and so on. I'm using the index to refer to each individual sheet.;;; – user234568 Oct 13 '19 at 16:46
@Mrblackey I uploaded the picture I'm talking about... The sheets are created and the names are specified clearly. I tested out with just 3 sheets from 5 to 7, but it's still showing the same despite the sheets are there in the excel.... – user234568 Oct 13 '19 at 17:05

Running sheets VBA

2 Answers2