0

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
Improve this question
3
  • 2
    You probably will want to take a look at this Commented Nov 26, 2018 at 18:58
  • 2
    Do you have 3 workbooks open when you run this? I'd suggest reading through How to avoid using Select in Excel VBA and tracking your object references. Commented Nov 26, 2018 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. Commented Nov 26, 2018 at 19:00

2 Answers 2

Reset to default
2

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"
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

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 :("

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.