I am trying to saving a file based on the text name in a designated cell in
the workbook. In other words, I am trying to save the open file as the name
that is in a cell in that open file's worksheet. Evidently, the code below
is not the way to approach it. I get an out of subscript error. Can anyone
point me in the right direction? It seems like this shouldn't be such a hard
thing, but I am baffled.



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
'bk1.saveas
'ActiveWorkbook.SaveCopyAs filename:= p & bk1
' ActiveWorkbook.SaveCopyAs filename:=path2 & "\" & bk1

Hi

This should do it.

Windows("fxRM_Update.xls").Activate
Dim SaveString As String
Dim sstr As String
Dim Path2 As String

Path2 = ActiveWorkbook.Path
sstr = Worksheets("lookup").Range("d39").Value
SaveString = Path2 & "\" & sstr & ".xls"
ActiveWorkbook.SaveCopyAs Filename:=SaveString

Regards,

Per

"Andyjim" skrev i en meddelelse
...



I am trying to saving a file based on the text name in a designated cell
in
the workbook. In other words, I am trying to save the open file as the
name
that is in a cell in that open file's worksheet. Evidently, the code
below
is not the way to approach it. I get an out of subscript error. Can
anyone
point me in the right direction? It seems like this shouldn't be such a
hard
thing, but I am baffled.



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
'bk1.saveas
'ActiveWorkbook.SaveCopyAs filename:= p & bk1
' ActiveWorkbook.SaveCopyAs filename:=path2 & "\" & bk1

hi,
you're missing someting.
sstr is not a workbook yet. so i would dump this line....
Set bk1 = Workbooks(sstr)
and replace it with....
ActiveWorkbook.SaveCopyAs Filename:=sstr & ".xls"

regards
FSt1

"Andyjim" wrote:




I am trying to saving a file based on the text name in a designated cell in
the workbook. In other words, I am trying to save the open file as the name
that is in a cell in that open file's worksheet. Evidently, the code below
is not the way to approach it. I get an out of subscript error. Can anyone
point me in the right direction? It seems like this shouldn't be such a hard
thing, but I am baffled.



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
'bk1.saveas
'ActiveWorkbook.SaveCopyAs filename:= p & bk1
' ActiveWorkbook.SaveCopyAs filename:=path2 & "\" & bk1

Name the cell where you have the full path and fielname as say rngFilename

then the only line of code you need is:

ActiveWorkbook.SaveAs Filename range("rngFileName").value

"Andyjim" wrote:




I am trying to saving a file based on the text name in a designated cell in
the workbook. In other words, I am trying to save the open file as the name
that is in a cell in that open file's worksheet. Evidently, the code below
is not the way to approach it. I get an out of subscript error. Can anyone
point me in the right direction? It seems like this shouldn't be such a hard
thing, but I am baffled.



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
'bk1.saveas
'ActiveWorkbook.SaveCopyAs filename:= p & bk1
' ActiveWorkbook.SaveCopyAs filename:=path2 & "\" & bk1

Thanks to both of you for such a speedy response! I will try this at the
first opportunity!

"DomThePom" wrote:

Name the cell where you have the full path and fielname as say rngFilename

then the only line of code you need is:

ActiveWorkbook.SaveAs Filename range("rngFileName").value

"Andyjim" wrote:




I am trying to saving a file based on the text name in a designated cell in
the workbook. In other words, I am trying to save the open file as the name
that is in a cell in that open file's worksheet. Evidently, the code below
is not the way to approach it. I get an out of subscript error. Can anyone
point me in the right direction? It seems like this shouldn't be such a hard
thing, but I am baffled.



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
'bk1.saveas
'ActiveWorkbook.SaveCopyAs filename:= p & bk1
' ActiveWorkbook.SaveCopyAs filename:=path2 & "\" & bk1