asked on

Excel Macro

Hello,

I have a small company and I use Excel to generate invoices using a template. I need to print Original as well as a Duplicate invoice every time I print an invoice. I have managed to use the below script and its working fine when I print it directly to the physical printer. But in case I need to save it as PDF using Adobe PDF or Cute PDF Writer as my default printer, the app prompts me for a file name at each page. Is there a way where in this can be achieved in a better way and this macro works even when i save it as pdf ? Is there a better way out to achieve what I am wanting to do. Can there be buttons like "Print Original"and a button "Print Duplicate".

PL not that I am not a pro in scripting but just manage to get things done.

Below is the macro I am using :

Sub PrintOrginalDuplicate()
ActiveSheet.PageSetup.RightHeader = "ORIGINAL COPY"
ActiveSheet.PrintOut
ActiveSheet.PageSetup.RightHeader = "DUPLICATE COPY"
ActiveSheet.PrintOut
ActiveSheet.PageSetup.RightHeader = ""
End Sub

Thank you. :)

Roy Cox

Which version of Excel are you using? Unless it's Excel 2003 or earlier then use Excel's own PDF function.

I would usually actually print a watermark style "Copy" rather than use the header

Advait Kawthalkar

ASKER

Hi Roy,

Am using Excel 2013. Thanks for your suggestion but watermark won't work. This are invoices which needs to be provided as per laws in which watermarks are not allowed.

Roy Cox

You can save the sheet as a pdf directly from Excel.

You cannot run the macro from a pdf.
Can you provide an example workbook or let me know which cell contains the Invoice Number. I would use the invoice number when saving a pdf copy

Advait Kawthalkar

ASKER

Hello Roy,

Please find attached excel sheet and 2 pdf that get created by running the macro. Press Ctrl + W to run the macro. Can we do something that it creates one pdf file with 2 pages ?
Book1.xlsm
Book1.pdf
Book2.pdf

Roy Cox

I have to go out this morning but I'll write some code for you as soon as I get back.

Advait Kawthalkar

ASKER

Thank you Roy.

ASKER CERTIFIED SOLUTION

Shums Faruk

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rob Henson

I can understand why you need to print two copies when hard printing, one to send and one to keep; but why do you need to create two pdf copies? Surely you are the only one that needs a PDF copy.

Just wondering???

Roy Cox

I've written code that copies creates a copy invoice an saves to a folder. I've also added code to increment the invoice number by one.

My usual method would be to create a database to store the invoice details - customer, values, etc.

Option Explicit


Sub CopyToPdf()
    Dim oWs As Worksheet
''///create temporary copy & set headers
    With Sheets(1)
        .Copy after:=Worksheets(1)
        ActiveSheet.Name = "Copy"
        ActiveSheet.PageSetup.RightHeader = "DUPLICATE COPY"
        .PageSetup.RightHeader = "ORIGINAL COPY"
    End With

''///sheets to print to pdf
    ThisWorkbook.Sheets(Array("Sheet 1", "Copy")).Select
''/// create pdf
    ActiveSheet.ExportAsFixedFormat _
            Type:=xlTypePDF, _
            Filename:="C:\TempFolder\" & Replace(ActiveSheet.Range("C7"), ":", "-") & ".pdf", _
            Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, _
            IgnorePrintAreas:=False, _
            OpenAfterPublish:=True
''/// clean up - remove copy sheet, clear header and increase invoice number by one
    Application.DisplayAlerts = False
    Sheets("Copy").Delete
    Sheets(1).PageSetup.RightHeader = ""
    Sheets(1).Range("C7").Value = "Invoice Number:" & Format(Val(Right(Sheets(1).Range("C7").Value, 3)) + 1, "000")
    Application.DisplayAlerts = True

End Sub

Open in new window

It might be an idea to store the invoice number in the Header as well, in which case use this code

Option Explicit


Sub CopyToPdf()
    Dim oWs As Worksheet
''///create temporary copy & set headers
    With Sheets(1)
        .Copy after:=Worksheets(1)
        ActiveSheet.Name = "Copy"
        ActiveSheet.PageSetup.RightHeader = "DUPLICATE COPY of " & Range("C7").Value
        .PageSetup.RightHeader = "ORIGINAL COPY of " & Range("C7").Value
    End With

''///sheets to print to pdf
    ThisWorkbook.Sheets(Array("Sheet 1", "Copy")).Select
''/// create pdf
    ActiveSheet.ExportAsFixedFormat _
            Type:=xlTypePDF, _
            Filename:="C:\TempFolder\" & Replace(ActiveSheet.Range("C7"), ":", "-") & ".pdf", _
            Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, _
            IgnorePrintAreas:=False, _
            OpenAfterPublish:=True
''/// clean up - remove copy sheet, clear header and increase invoice number by one
    Application.DisplayAlerts = False
    Sheets("Copy").Delete
    Sheets(1).PageSetup.RightHeader = ""
    Sheets(1).Range("C7").Value = "Invoice Number:" & Format(Val(Right(Sheets(1).Range("C7").Value, 3)) + 1, "000")
    Application.DisplayAlerts = True

End Sub

Open in new window

InvoiceToPDF-.xlsm

Roy Cox

Hi Rob

I understood the request to be one pdf with the original & copy in. Then you can print both or individual sheets as required.

Advait Kawthalkar

ASKER

Thank you all for your help. I think Shums was the first to give a solution which is working completely and satisfies my need. So selecting his solution.

Roy Cox

So what about my code which not only does with no unnecessary lines of code what you asked but also increments your invoice number. First is not always the best.