Run-time Error '9' - subscript out of range

Question

When running my macro, I keep getting a run-time error stating the subscript is out of range. Anyone have any pointers? The line that is not working is the Application.Workbooks(3).Activate

Below is the macro..

Sub Macro1()
'
' Macro1 Macro
'

'
  ActiveSheet.Unprotect
  ActiveSheet.Name = "M-YTD"
    Range("E16:H16").Select
    Selection.MergeCells = False
    Columns("B:G").Select
    Range("G11").Activate
    Selection.Copy
    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Paste
    ActiveSheet.Name = "VarianceRpt"
    Rows("1:10").Select
    Range("A10").Activate
    Selection.EntireRow.Hidden = True
    Columns("G:G").ColumnWidth = 50
    Range("G18").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "Variance Notes"
    Range("F19").Select
    Selection.Copy
    Range("G18:G19").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Range("D16:F16").Select
    Selection.MergeCells = True
    ActiveSheet.Previous.Select
    Range("E16:H16").Select
    Selection.MergeCells = True
    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Name = "Transfers"
    Windows("Var Template.xls").Activate
    Range("A1:M37").Select
    Selection.Copy
    Application.Workbooks(3).Activate
    ActiveSheet.Paste
    Sheets("VarianceRpt").Select
    Sheets("VarianceRpt").Move Before:=Sheets(1)
End Sub

You probably will want to take a look at [this](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — cybernetic.nomad, Nov 26 '18 at 18:58
Do you have 3 workbooks open when you run this? I'd suggest reading through [How to avoid using Select in Excel VBA](https://stackoverflow.com/q/10714251/4088852) and tracking your object references. — Comintern, Nov 26 '18 at 18:58
You need to explicitly state what you mean by `Workbooks(3)`. Recording a macro is a great place for beginners to get a starting point, but these tend to require a lot of editing down and tweaking to get to the final product. — urdearboy, Nov 26 '18 at 19:00

David Zemens · Answer 1 · 2018-11-26T19:15:15.927

This is essentially a typo. a Subscript out of Range error always means that you're trying to index an array/collection with an invalid index parameter, in other words, the index you're asking for doesn't exist because it's outside the bounds of the sequence.

Application.Workbooks(3) means the third Workbook object belonging to the current Application instance. If the current Application instance contains 2 or fewer Workbooks, then any index value greater than or equal to 3 will raise the error. Likewise, an index of 0 will raise the same error.

The same is true for any Collection type, the index parameter must be greater than 0, and less than or equal to the length of the collection. So:

ThisWorkbook.Worksheets(0) will fail since 0 is not a valid index
ThisWorkbook.Worksheets(13) will fail if there are 12 or fewer Worksheets
ActiveSheet.ListObjects(1) will fail if there aren't at least one ListObject on the worksheet

Arrays are usually base-zero, which means that their indices begin at 0, as a result their upper bound is 1 less than their length, so an array like:

Dim myArray
myArray = Array("A", "B", "C", "D")

Will have an upper bound of 3, and a lower bound of 0. Valid indices are therefore within the range of 0 to 3, so:

myArray(4) will raise the error
myArray(0) will return the value "A"

One sort-of exception is the Range.Cells collection, which actually works differently:

ActiveSheet.Range("A1").Cells(2) refers to cell "A2"

score 0 · Answer 2 · answered Nov 26 '18 at 19:05

0

In human language this error would be "You want me (Excel, or Application) to go to the third open workbook and activate it. But I have less than 3 workbooks. So, I show you this ugly error. Sorry :("

answered Nov 26 '18 at 19:05

Nick

83
7

Run-time Error '9' - subscript out of range

2 Answers2