Excel – ‘Subscript out of range’ error when selecting a range

microsoft-excel-2007vba

I'm getting a VBA error (Run-time error '9': Subscript out of range) when I am trying to select a workbook and then a cell within a sheet in that workbook.

this_workbook is correct, and copying and pasting the output into Windows Run results in the workbook opening. data_worksheet is also correct.

this_workbook = ThisWorkbook.Path & "\" & ThisWorkbook.Name

Debug.Print "Workbook name - " & this_workbook & vbCrLf
Workbooks(this_workbook).Sheets(data_worksheet).Range("A1").Select

Anybody know what I am doing wrong?

Best Answer

You cannot select a Range in a Worksheet that is not active.

Additionally, calling using Workbook(this_workbook) overly complicates your code.

Try replacing your code with this (Assumes data_worksheet represents a worksheet name or number):

With Thisworkbook.Sheets(data_worksheet).
    .Activate
    .Range("A1").select
End with

Now bear in mind that this code doesn't actually do anything other than select a cell in a particular sheet, so this macro isn't particularly useful.

Related Question