Run time error '9' VBA

Question

I get the error 'Subscript Out of Range' when I run the following code; debug points me to the last line:

Dim SrcBook As Workbook
Dim TrgBook As Workbook
Dim SrcSheet As Worksheet
Dim TrgSheet As Worksheet
Dim Sheet_Name As String

Workbooks.Open (CalendarFile)
Sheet_Name = MonthName(Month(SrcSheet.Cells(SrcRow, "D").Value), False)
MsgBox ("Sheet_Name Value is: " & Sheet_Name)
Set TrgSheet = Workbooks(CalendarFile).Worksheets(Sheet_Name)

I have repeatedly verified that CalendarFile is a valid file name (I use the full path filename). Sheet_Name is also the valid name of a sheet in that Workbook. I get a similar error if I try to access the worksheets via numeric indexing [ie, Workbooks(CalendarFile).Worksheets(11) vs Workbooks(CalendarFile).Worksheets(November)]. The MsgBox call verifies that I'm feeding the WorkSheets() method the proper sheet name.

Lastly, ScrRow is properly defined - I am able to use this code to manipulate target WorkSheets in the same WorkBook as the macro is called from in toy/testing applications, but for some reason it is failing when I try to manipulate target WorkSheets in other (open) WorkBooks.

Any help would be greatly appreciated! Thank You!

@Niclas why would it be a Range? MonthName returns a string. — Sorceri

Comintern Comintern · Accepted Answer · 2016-12-02T21:38:31

If CalendarFile is a valid filename, that's your issue. The index you need for Workbooks() is the Workbook.Name, not its file path. For example, if CalendarFile was C:\Foo\Bar.xlsx, you need to use Bar.xlsx.

As for the explanation above, it really doesn't matter because you should really just grab a the reference that Workbooks.Open returns and just use that:

Set TrgBook = Workbooks.Open(CalendarFile)
Sheet_Name = MonthName(Month(SrcSheet.Cells(SrcRow, "D").Value), False)
MsgBox ("Sheet_Name Value is: " & Sheet_Name)
Set TrgSheet = TrgBook.Worksheets(Sheet_Name)

Run time error '9' VBA

2 Answers