Spreadsheet save using a macro
 

I created a macro to save a spreadsheet from a template. I want to use the
cell "invoice number" as the name when doing the save. I have not been able
to figure out how to tell the macro to use a specific cell for a name save.

Spreadsheet save using a macro
 

Hi KM,

Try something like:

'===================
Public Sub Tester()
Dim WB As Workbook
Dim SH As Worksheet
Dim aCell As Range
Dim sPath As String

sPath = Application.DefaultFilePath '<<======== CHANGE
Set WB = ActiveWorkbook '<<======== CHANGE
Set SH = WB.Sheets("Sheet1") '<<======== CHANGE
Set aCell = SH.Range("Invoice_Number") '<<======== CHANGE

WB.SaveAs CStr(aCell.Value) & ".xls", FileFormat:=xlNormal

End Sub
'<<===================

---
Regards,
Norman



"kmdaly" wrote in message
...
I created a macro to save a spreadsheet from a template. I want to use the
cell "invoice number" as the name when doing the save. I have not been
able
to figure out how to tell the macro to use a specific cell for a name
save.

Spreadsheet save using a macro
 

Hi Norman,
Thanks so much for the help. However, I am still having problems. I am
getting a runtime error 1004 on the last line of code. This workbook is a
template that updates another spreadsheet. The update seems to work in that
it writes a record (using wizard template functionality) but I error at the
last stmt "method "save as' of object '_ workbook failed" any insights are
greatly appreicated.

"Norman Jones" wrote:

Hi KM,

Try something like:

'===================
Public Sub Tester()
Dim WB As Workbook
Dim SH As Worksheet
Dim aCell As Range
Dim sPath As String

sPath = Application.DefaultFilePath '<<======== CHANGE
Set WB = ActiveWorkbook '<<======== CHANGE
Set SH = WB.Sheets("Sheet1") '<<======== CHANGE
Set aCell = SH.Range("Invoice_Number") '<<======== CHANGE

WB.SaveAs CStr(aCell.Value) & ".xls", FileFormat:=xlNormal

End Sub
'<<===================

---
Regards,
Norman



"kmdaly" wrote in message
...
I created a macro to save a spreadsheet from a template. I want to use the
cell "invoice number" as the name when doing the save. I have not been
able
to figure out how to tell the macro to use a specific cell for a name
save.

Spreadsheet save using a macro
 

Okay, I got the macro to work for one part of the scenario. But could use
additional assistance. I am using a template that when saved should update an
Excel DB (spreadsheet defined using Template wizard) with new records, save
to a new worksheet (this part is working) and location; then clear the
template for the next entry. I can get the record update to work (write
record to DB) or I can get the macro to create a new worksheet and save it
but I can't seem to get them to work together. Any help is welcome
"kmdaly" wrote:



"Norman Jones" wrote:

Hi KM,

Try something like:

'===================
Public Sub Tester()
Dim WB As Workbook
Dim SH As Worksheet
Dim aCell As Range
Dim sPath As String

sPath = Application.DefaultFilePath '<<======== CHANGE
Set WB = ActiveWorkbook '<<======== CHANGE
Set SH = WB.Sheets("Sheet1") '<<======== CHANGE
Set aCell = SH.Range("Invoice_Number") '<<======== CHANGE

WB.SaveAs CStr(aCell.Value) & ".xls", FileFormat:=xlNormal

End Sub
'<<===================

---
Regards,
Norman



"kmdaly" wrote in message
...
I created a macro to save a spreadsheet from a template. I want to use the
cell "invoice number" as the name when doing the save. I have not been
able
to figure out how to tell the macro to use a specific cell for a name
save.

Spreadsheet save using a macro
 

Norman,
I hate to be dense, but I am in a time crunch here and need further
information, if you don't mind.
I am not familiar with VBscripting and I am having trouble recording a macro
to save one specific worksheet out of a workbook.

I found this post, but do not know what to substitute for
Application.DefaultFilePath...I think I know the values I need to replace for
the rest.
Can you help me or guide me to more information about writing macros?

Thanks,
--
Jean


"Norman Jones" wrote:

Hi KM,

Try something like:

'===================
Public Sub Tester()
Dim WB As Workbook
Dim SH As Worksheet
Dim aCell As Range
Dim sPath As String

sPath = Application.DefaultFilePath '<<======== CHANGE
Set WB = ActiveWorkbook '<<======== CHANGE
Set SH = WB.Sheets("Sheet1") '<<======== CHANGE
Set aCell = SH.Range("Invoice_Number") '<<======== CHANGE

WB.SaveAs CStr(aCell.Value) & ".xls", FileFormat:=xlNormal

End Sub
'<<===================

---
Regards,
Norman



"kmdaly" wrote in message
...
I created a macro to save a spreadsheet from a template. I want to use the
cell "invoice number" as the name when doing the save. I have not been
able
to figure out how to tell the macro to use a specific cell for a name
save.