add function on print

[niyrho]

I have a work order form in excel. I want to set it up so that when you hit the print button it will take the info from two spacific cells and use them as a file name and save the work order to a certain folder.

For example, you click the print icon and it will look at the customer name and the date and save the work order as "customer 07/05/08" to the folder "work orders" or whatever. Then is prints the page and clears it for you so you have the blank work order in excel still.

[mdmackillop]

Hi niyrho
Welcome to VBAX.
Place the following code in ThisWorkbook module. Data is assumed to be in Cells A1 & A2.
Using date format yy-mm-dd in the file name allows for easier sorting of saved files in date order.
[vba]
Private Sub Workbook_BeforePrint(Cancel As Boolean)
On Error GoTo Exits
Cancel = True
Application.EnableEvents = False
With ActiveSheet
ActiveWorkbook.SaveCopyAs "C:\Work Orders\" & .Range("A1") & Format(.Range("A2"), " yy-mm-dd") & ".xls"
.PrintOut
.Range("A1:A2").ClearContents
End With
Exits:
Application.EnableEvents = True
End Sub

[/vba]

[niyrho]

Thanks, that code worked perfect. But now I need one lil function added to it. Is there a way to have it print out only the pages with information entered them? These are for work orders and inspection reports, so there are things on each page anyway, so I guess it would have to ignore any locked cells. I don't know.

[mdmackillop]

Try recording a macro printing what you want to print and post the result.