Print Multiple invoices in one PDF File

[Derar]

I have created a small excel application in which I can bill clients water utility charges on a monthly basis considering a new water meter reading for every month.
I was able to convert multiple lines to invoices and export the same to PDF, but that make a hassle for me as the application will create a plenty of PDF files and found difficulties to print them
In fact, I need a code to support my codes that will enable me to make all invoices in PDF file (each invoice in a separate page or 2 invoices in one)

Best regards

Sub Button1_Click()

Dim invoiceNumber As Integer
Dim clientName As String
Dim MeterNo As String
Dim clientPhone As String
Dim GroupNO As String
Dim SubscriberNo As String
Dim CycleNo As String
Dim PreviousReading As String
Dim CurrentReading As String
Dim Consumption As String
Dim invoicingPrice As Currency
Dim startTableRow As Integer
Dim InvoiceFile As String
Dim ReadingDate As String
Dim InvoicingDate As Date
Dim x As Integer
Dim y As Integer
Dim t As Integer
Dim saveLocation As String



'Loop through and find which line items haven't been invoiced yet
For x = 2 To 20000
'If Invoice Sent is blank and there is a date in the first column
If Worksheets("Invoicing").cells(x, 10) = "" And _
Worksheets("Invoicing").cells(x, 2) <> "" Then
invoiceNumber = Worksheets("Invoicing").cells(x, 2).Value

'Set first lines on the Invoice
Worksheets("Invoice").cells(9, 2).Value = invoiceNumber
Worksheets("Invoice").cells(3, 10).Value = "" & _
Format(Date, "dd/mm/yyyy")

'Loop through Main sheet to find Details
clientName = Worksheets("Invoicing").cells(x, 5).Value
For y = 2 To 20000
If Worksheets("Main").cells(y, 2).Value = clientName Then
MeterNo = Worksheets("Main").cells(y, 3)
clientPhone = Worksheets("Main").cells(y, 7)
GroupNO = Worksheets("Main").cells(y, 4)
CycleNo = Worksheets("Invoicing").cells(x, 1)
CurrentReading = Worksheets("Invoicing").cells(x, 6)
PreviousReading = Worksheets("Invoicing").cells(x, 7)
Consumption = Worksheets("Invoicing").cells(x, 8)
invoicingPrice = Worksheets("Invoicing").cells(x, 9)
ReadingDate = Worksheets("Invoicing").cells(x, 3)



Exit For
End If
Next y


'Set the corresponding values on the Invoice sheet
Worksheets("Invoice").cells(5, 6).Value = clientName
Worksheets("Invoice").cells(6, 3).Value = MeterNo
Worksheets("Invoice").cells(5, 10).Value = clientPhone
Worksheets("Invoice").cells(6, 6).Value = GroupNO
Worksheets("Invoice").cells(6, 10).Value = CycleNo
Worksheets("Invoice").cells(11, 3).Value = CycleNo
Worksheets("Invoice").cells(9, 3).Value = CurrentReading
Worksheets("Invoice").cells(9, 5).Value = PreviousReading
Worksheets("Invoice").cells(9, 6).Value = Consumption
Worksheets("Invoice").cells(9, 8).Value = invoicingPrice
Worksheets("Invoice").cells(9, 10).Value = ReadingDate



'Loop through the Invoicing sheet and as long as the invoice number doesn't _
'change, write it to the table on the invoice
startTableRow = 9
Do While Worksheets("Invoicing").cells(x, 2).Value = invoiceNumber
ReadingDate = Worksheets("Invoicing").cells(x, 3).Value
'invoicingService = Worksheets("Invoicing").cells(x, 3).Value
MeterNo = Worksheets("Invoicing").cells(x, 4).Value


Worksheets("Invoicing").cells(x, 10).Value = "Yes"
x = x + 1
startTableRow = startTableRow + 1
Loop

'Need to subtract one from x to loop through the row again
x = x - 1

Worksheets("Invoice").Activate
InvoiceFile = ActiveWorkbook.Path & "/" & ActiveSheet.Name & "_" & _
invoiceNumber & ".pdf"

'Export activesheet as PDF
With ActiveSheet
.ExportAsFixedFormat Type:=xlTypePDF, Filename:=InvoiceFile, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=False
End With

saveLocation = "D:\Personal\Syrian Government Documents Software\DAWSSA\Invoices\ & invoiceNumber & .pdf"

'Clear data in table on invoice
'For t = 9 To 12
Worksheets("Invoice").cells(3, 10).Value = ""
Worksheets("Invoice").cells(5, 6).Value = ""
Worksheets("Invoice").cells(5, 10).Value = ""
Worksheets("Invoice").cells(6, 3).Value = ""
Worksheets("Invoice").cells(6, 6).Value = ""
Worksheets("Invoice").cells(6, 10).Value = ""
Worksheets("Invoice").cells(9, 2).Value = ""
Worksheets("Invoice").cells(9, 3).Value = ""
Worksheets("Invoice").cells(9, 5).Value = ""
Worksheets("Invoice").cells(9, 6).Value = ""
Worksheets("Invoice").cells(9, 8).Value = ""
Worksheets("Invoice").cells(9, 10).Value = ""
Worksheets("Invoice").cells(11, 3).Value = ""
' Next t

End If

Next x

End Sub

 

[John_w]

It's difficult to help you without sample worksheets on which your code operates. Also, post your code inside VBA code tags (click the VBA icon on the message editor toolbar) to keep the indentation and make it easier to read, like this:

[CODE=vba]your code here[/CODE]

See if these changes work for you. Instead of creating multiple PDFs for the "Invoice" sheet, it copies the "Invoice" sheet to a new sheet, groups all the new sheets and exports them as a single PDF named "All_Invoices.pdf". It then deletes the new sheets.

Add the following code above the For x = 2 To 20000:

    Dim PDFsheets As String
    PDFsheets = ""

    Application.ScreenUpdating = False

Replace the following code:

            Worksheets("Invoice").Activate
            InvoiceFile = ActiveWorkbook.Path & "\" & ActiveSheet.Name & "_" & invoiceNumber & ".pdf"

            'Export activesheet as PDF
            With ActiveSheet
                .ExportAsFixedFormat Type:=xlTypePDF, Filename:=InvoiceFile, _
                Quality:=xlQualityStandard, IncludeDocProperties:=True, _
                IgnorePrintAreas:=False, OpenAfterPublish:=False
            End With

with:

            Worksheets("Invoice").Copy After:=Worksheets(Worksheets.Count)
            PDFsheets = PDFsheets & ActiveSheet.Name & ","

Add the following code after the Next x:

    If PDFsheets <> "" Then
        Worksheets(Split(Left(PDFsheets, Len(PDFsheets) - 1), ",")).Select
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ActiveWorkbook.Path & "\All_Invoices.pdf", _
            Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
 
        Application.DisplayAlerts = False
        ActiveWindow.SelectedSheets.Delete
        Application.DisplayAlerts = True
    End If

    Application.ScreenUpdating = True