Save and print macro...

[gav5gtt]

Hi,

I'm trying to create an invoice with a button in so that the user can click the button when all data is entered to save to a file (it needs to pick up the invoice number automatically from the form) and print a copy off.

Any ideas? I've seen it done before, but i havent got a clue how to do it???

Cheers
Gav

[Tom Schreiner]

Hi
You want to save the invoice as a seperate file with the file name being the invoice number and then print out the invoice?
Do you need data validation before allowing the save and print?
Thanks,
Tom

[gav5gtt]

Yes, I want to save the invoice as a seperate file with the file name being the invoice number and then print out the invoice.

Theres no need for the data to be validated first though - only a fairly simple invoice (for now)

[Tom Schreiner]

Hi
Play around with this and see if it will help you...

Sub SaveInvoice()
Dim NewFilePath As String
Dim InvoiceNumber As String
NewFilePath = "C:Test" ' Assign your path
InvoiceNumber = Range("A1") ' or whichever cell contains the invoice number
ActiveSheet.PrintOut
ActiveSheet.Copy
ActiveWorkbook.SaveAs NewFilePath & "" & InvoiceNumber & ".xls"
MsgBox "Invoice Number " & InvoiceNumber & _
" saved as: " & NewFilePath & "" & InvoiceNumber & ".xls"
ActiveWorkbook.Close
End Sub

Tom

[gav5gtt]

Cheers mate - that works great.

Next question - How can i get the form to generate a new invoice number every time ie - last invoive was 1001 so next will automatically be saved as 1002???

Is this possible or is there another way to do something similar?

[Tom Schreiner]

Hi
If the invoice will be saved every time, just add this line of code to the above procedure directly above this line:

InvoiceNumber = Range("A1")

One again, replace A1 with the actual cell with the invoice number...

Range("A1") = Range("A1") + 1
InvoiceNumber = Range("A1")

Tom

[gav5gtt]

Cheers again...

What about when he runs the macro, it will automatically insert the date and time into the form and save it as eg 1001_22-04-02.xls

??

PS - forgive my questions, i'm new to the vb scene. I've had "vb for dummies" for about 4 months now and still haven't touched it - i find it easier to learn like this!!

Cheers
Gav

[Tom Schreiner]

I'm not sure exactly what you are needing?
Is 1001_22-04-02 the invoice number?
Or
Invoice number plus date for just saving as the filename?

If all you are needing is to collate the invoice number and date to save the file then replace the following two lines:

ActiveWorkbook.SaveAs NewFilePath & "" & InvoiceNumber & ".xls"

MsgBox "Invoice Number " & InvoiceNumber & _
" saved as: " & NewFilePath & "" & InvoiceNumber & ".xls"

with these:

ActiveWorkbook.SaveAs NewFilePath & "" & InvoiceNumber & _
"_" & Format(Now, "DD-MM-YY") & ".xls"

MsgBox "Invoice Number " & InvoiceNumber & _
" saved as: " & NewFilePath & "" & InvoiceNumber & _
"_" & Format(Now, "DD-MM-YY") & ".xls"

Of course, you do not need the message box...

Is this what you are after?

Tom

[gav5gtt]

Fantastic. All works a treat

Nearly finished... All i need to do now is to insert the date into the form itself so that it updates itself, and to place a button on the form that actions the macro.

Gav