SaveAs produces subscript 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. Do you know what I am doing wrong?

Thanks for your help.

Windows("fxRM_Update.xls").Activate

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)

ActiveWorkbook.SaveCopyAs filename:=Workbooks(bk1)

Andy

 

[Matthew Pfluger]

Which line produces the error? Maybe the value in cell D39 is invalid.

Matthew Pfluger

 

[Andyjim]

Hi Matthew-

Actually, I have a similar macro that works (see below). And actually, I
don't think the saveas code is the problem. It seems to error on the line:

Set bk1 = Workbooks(sstr)

Here is the similar macro that works:

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

Thanks for your help

 

[JLGWhiz]

It could be the file extension that is causing the problem, if the value in
d39 does not have one.

Try:

Set bk1 = Workbooks(sstr & ".xls") 'modify for xl2007
ActiveWorkbook.SaveCopyAs filename:=bk1