Reactivating file based on text value producees subscript error

[Andyjim]

Hi-
I've gotten tremendous help from you before. I hope you can help me here.

I use the following code to reactivate an open file to which we don't know
the name, but the name exists in the form of text in a cell in the currently
active file called fxRM_Update.xls.
This code produces a subscript out of range error.

Sub Update2()
'Defines user file in fxRM_Update.xls
'Update macro calls this macro each time it must return to user file

Dim bk As Workbook, bk1 As Workbook
Dim sstr As String
Set bk = Workbooks("fxRM_Update.xls")
sstr = bk.Worksheets("lookup").Range("d39").Value
Set bk1 = Workbooks(sstr)
bk1.Activate

End Sub

I would be very grateful if you could help me.

Andy

 

[Jim Rech]

Whenever I get a subscript out of range error it is inevidentably because
the the workbook or worksheet or whatever I'm supplying the text name for
does not exist in the given context. You could have helped us by telling
which line in your code produces the error but I think we can safely say
that either:

-there is no workbook named "fxrm_update.xls" open.
-if there is, it does not have a worksheet named "lookup".
-if it does, there is no workbook open with the name in cell d39 of that
sheet.

Excel is not case sensitive in these things but it is otherwise quite
literal, so you can't omit the ".xls" from a workbook name for example.