Subscript Out Of Range Error

[MikeG]

I have a workbook called "Project" and in it there is a macro that pulls data out of another workbook called "Operations Report."

The macro works fine on my laptop. When I run the macro, I make sure that both workbooks are open, (and they are also saved in the same directory - not sure that matters).

I sent the two workbooks to a colleague and when she opens them both and runs the macro, she gets a "run error 9 subscript out of range" error. She too has saved them to the same directory.

Can anyone suggest why she might be getting the error when I do not? Here is the line where hers trips up:

With Workbooks("Operations Report").Worksheets("Sheet2")

I assume that the macro is not finding the "Operations Report" workbook.

Are there some checks we could do or test lines of code I could send to her to diagnise the problem?

Thanks,

MikeG

 

[Gary McMaster]

Just a guess:

Your co-worker has the two books open in two different instances of Excel?

Gary

 

[GTO]

Have your friend try including the extension:

With Workbooks("Operations Report.xls").Worksheets("Sheet2")

 

[MikeG]

Thanks Gary and GTO - I'll check both of these and get back.

MikeG

 

[MikeG]

Unfortunately, she still gets the same area - even with the extension. And she is only running one incidence of Excel.

By the way, we are both using Excel 2007.

Mike

 

[Gary McMaster]

I tried it with XL2000. I found I need the .xls extension and both workbooks open in the same instance of Excel before I could switch between the two books via code.

Can't think of anything else to try at the moment.

Gary

 

[GTO]

I agree with Gary. Other than the obvious (she renamed teh wb), I wonder if she inadvertantly is getting two instances.

In the wb w/the code, have her run:

Sub ListWBs()
Dim wb As Workbook
    
    For Each wb In Workbooks
        MsgBox ">" & wb.Name & "<"
        ' or Debug.Print ">" & wb.Name & "<"
    Next
End Sub

 

[MikeG]

I agree with Gary. Other than the obvious (she renamed teh wb), I wonder if she inadvertantly is getting two instances.

In the wb w/the code, have her run:

Sub ListWBs()
Dim wb As Workbook
    
    For Each wb In Workbooks
        MsgBox ">" & wb.Name & "<"
        ' or Debug.Print ">" & wb.Name & "<"
    Next
End Sub

Thanks - nice macro.

I've put it in the workbook and sent to my co-worker.

Mike

 

[MikeG]

Thanks - nice macro.

I've put it in the workbook and sent to my co-worker.

Mike

GTO:

My co-worker just ran the macro and it did list the "Operations Report.xls" workbook - so it does seem to be available. (I was hoping it wouldn't, because that might have made it easier to solve."

Thanks

MikeG

 

[John Davis]

Unfortunately, she still gets the same area - even with the extension. And she is only running one incidence of Excel.

By the way, we are both using Excel 2007.

Mike

Maybe try referencing the sheet differently?

With Workbooks("Operations Report.xls").Sheets("Sheet2")