Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
    
Have you seen this? –  pnuts Oct 18 '12 at 16:06
    
FYI: If your question is specifically about code you have written, (like this one) you will get a broader audience willing and able to answer by asking on Stack Overflow instead of Super User. –  Daniel Cook Oct 18 '12 at 16:20
    
@DanielCook - I have that dilemma every time I post about VBA. I've been told the exact oppisite in the past, and I have found that, as the majority of Office experts roam this forum, the answers may be fewer, but are often better. –  David Gard Oct 19 '12 at 8:53

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
    
Thanks for you help. Seeing that you activated the sheet within the workbook rather than just the workbook made it click, as I was on the wrong sheet! And FYI, the macro does do more than this, this is just the tidying up at the end of a function. –  David Gard Oct 19 '12 at 8:50
    
I was sure that you would eventually do something else. You just didn't share that part. I hope you get the rest working without problems. –  Daniel Cook Oct 19 '12 at 14:56

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.