SaveAs produces subscript error

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Andyjim - 24 Jan 2008 18:02 GMT

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

Reply to this Message

Matthew Pfluger - 24 Jan 2008 18:17 GMT

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

Matthew Pfluger

> 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”
[quoted text clipped - 13 lines]
>
> Andy

Reply to this Message

Andyjim - 24 Jan 2008 18:50 GMT

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

Reply to this Message

JLGWhiz - 24 Jan 2008 21:54 GMT

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

> 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”
[quoted text clipped - 13 lines]
>
> Andy

Reply to this Message