Variable causing subscript out of range error

[Andyjim]

I am trying to saving a file based on the text name in a designated cell in
the workbook. However, with this code, I get an “subscript out of rangeâ€
error. I am running out of ideas. There must be a way to do this! Seems
to be problem with variable.


Thanks for your help.

Windows("fxRM_Update.xls").Activate

Dim bk As Workbook, bk1 As Workbook
Dim sstr As String
Dim path2 As String
path2 = ActiveWorkbook.Path

Set bk = Workbooks("fxRM_update.xls")
sstr = bk.Worksheets("lookup").Range("d39").Value
Set bk1 = Workbooks(sstr) THIS IS WHERE SUBSCRIPT ERROR OCCURS

ActiveWorkbook.SaveCopyAs filename:= bk1
'bk.saveas
'ActiveWorkbook.SaveCopyAs filename:= p & bk1
' ActiveWorkbook.SaveCopyAs filename:=path2 & "\" & bk1

A similar macro works fine:

Sub update4() 'defines user file in fxRM_Update.xls Need to insert this
everytime you reactivate fxRM_Update and want to refer back 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

 

[Jim Thomlinson]

Your issue is gong to be that there is no workbook open that matches the
value sstr. Add a message box just before the line that sets the object
reference to confirm the value of sstr. You will need it to match exactly to
the name of the open workbook. Check for blank spaces and such...

sstr = bk.Worksheets("lookup").Range("d39").Value
msgbox sstr 'Check the value of the variable
Set bk1 = Workbooks(sstr) THIS IS WHERE SUBSCRIPT ERROR OCCURS