Macro to Save As with filename based on cell contents.

[imafellow]

I've been scouring the groups looking for a solution to this and I've
found many people with similar problems but none of the solutions
proposed work for me. I'm sure it's my lack of familiarity with VBA and
its implementation that's holding me back.
Here's what I've got:

I have and invoice with the invoice number in cell C7 and other data in
A15. I want to run a macro that will save the current open document to,
say, D:\Work\Accounting\Invoices. I want the file name to be:

Invoice - [invoice number] - [other data].xls

I tried methods along the lines of this:
ActiveWorkbook.SaveAs Filename:="D:\Work\Accounting\Invoices\Invoice -
" & Worksheets("Service Invoice").Range("C7").Value & " - " &
Worksheets("Service Invoice").Range("A15").Value & ".xls"

but I get errors and no saved files.

Once I get a working script I need to know in which module it goes.

Can anyone lend a feller a hand?

]-[

 

[Norman Jones]

Hi ImAFellow,

Try:

'=============>>
Public Sub TesterZ()
Dim WB As Workbook
Dim SH As Worksheet
Dim sStr1 As String
Dim sStr2 As String
Const myPath As String = "D:\Work\Accounting\Invoices\"


Set WB = ThisWorkbook
Set SH = WB.Sheets("Service Invoice")
sStr1 = SH.Range("C7").Value
sStr2 = SH.Range("A15").Value

WB.SaveAs Filename:=myPath & "Invoice - " & sStr1 _
& " - " & sStr2 & ".xls", _
FileFormat:=xlWorkbookNormal

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


This code should be pasted into a standard module. In the VBE:

Menus | Insert | Module

 

[imafellow]

Thanks, Norman. I think I see the logic behind what you've done,
although I'm a bit confused by the syntax. I'll plug it in and report
back.

]-[

Hi ImAFellow,

Try:

'=============>>
Public Sub TesterZ()
Dim WB As Workbook
Dim SH As Worksheet
Dim sStr1 As String
Dim sStr2 As String
Const myPath As String = "D:\Work\Accounting\Invoices\"


Set WB = ThisWorkbook
Set SH = WB.Sheets("Service Invoice")
sStr1 = SH.Range("C7").Value
sStr2 = SH.Range("A15").Value

WB.SaveAs Filename:=myPath & "Invoice - " & sStr1 _
& " - " & sStr2 & ".xls", _
FileFormat:=xlWorkbookNormal

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


This code should be pasted into a standard module. In the VBE:

Menus | Insert | Module


---
Regards,
Norman

I've been scouring the groups looking for a solution to this and I've
found many people with similar problems but none of the solutions
proposed work for me. I'm sure it's my lack of familiarity with VBA and
its implementation that's holding me back.
Here's what I've got:

I have and invoice with the invoice number in cell C7 and other data in
A15. I want to run a macro that will save the current open document to,
say, D:\Work\Accounting\Invoices. I want the file name to be:

Invoice - [invoice number] - [other data].xls

I tried methods along the lines of this:
ActiveWorkbook.SaveAs Filename:="D:\Work\Accounting\Invoices\Invoice -
" & Worksheets("Service Invoice").Range("C7").Value & " - " &
Worksheets("Service Invoice").Range("A15").Value & ".xls"

but I get errors and no saved files.

Once I get a working script I need to know in which module it goes.

Can anyone lend a feller a hand?

]-[

 

[imafellow]

Norman, when I run the script I get an error "400" with no explanation.
I've double- and triple-checked the myPath path (which is actually
"D:\WORK\=ACCOUNTING\Invoices Pending", and not as I specified in my
first post) and it checks out. So does the worksheet name.

What could the problem be?

]-[

Hi ImAFellow,

Try:

'=============>>
Public Sub TesterZ()
Dim WB As Workbook
Dim SH As Worksheet
Dim sStr1 As String
Dim sStr2 As String
Const myPath As String = "D:\Work\Accounting\Invoices\"


Set WB = ThisWorkbook
Set SH = WB.Sheets("Service Invoice")
sStr1 = SH.Range("C7").Value
sStr2 = SH.Range("A15").Value

WB.SaveAs Filename:=myPath & "Invoice - " & sStr1 _
& " - " & sStr2 & ".xls", _
FileFormat:=xlWorkbookNormal

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


This code should be pasted into a standard module. In the VBE:

Menus | Insert | Module


---
Regards,
Norman

I've been scouring the groups looking for a solution to this and I've
found many people with similar problems but none of the solutions
proposed work for me. I'm sure it's my lack of familiarity with VBA and
its implementation that's holding me back.
Here's what I've got:

I have and invoice with the invoice number in cell C7 and other data in
A15. I want to run a macro that will save the current open document to,
say, D:\Work\Accounting\Invoices. I want the file name to be:

Invoice - [invoice number] - [other data].xls

I tried methods along the lines of this:
ActiveWorkbook.SaveAs Filename:="D:\Work\Accounting\Invoices\Invoice -
" & Worksheets("Service Invoice").Range("C7").Value & " - " &
Worksheets("Service Invoice").Range("A15").Value & ".xls"

but I get errors and no saved files.

Once I get a working script I need to know in which module it goes.

Can anyone lend a feller a hand?

]-[

 

[Norman Jones]

Hi ImAFellow,

Norman, when I run the script I get an error "400" with no
explanation.I've double- and triple-checked the myPath path
(which is actually "D:\WORK\=ACCOUNTING\Invoices Pending",
and not as I specified in my first post) and it checks out. So does
the worksheet name.

What could the problem be?

I regret that I can only refer you to a post by Tom Ogilvy:

http://tinyurl.com/kask3