SaveAs produces subscript error
 

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

SaveAs produces subscript error
 

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

Matthew Pfluger

"Andyjim" wrote:

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

SaveAs produces subscript error
 

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

SaveAs produces subscript error
 

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





"Andyjim" wrote:

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