saving open workbook to text string in cell
 

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

saving open workbook to text string in cell
 

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

saving open workbook to text string in cell
 

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

saving open workbook to text string in cell
 

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

saving open workbook to text string in cell
 

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