VBA to print as single PDF

[sjp1979]

Hi All

I have a document that creates multiple documents in a mail merge type action.

I'd like these output as a single PDF for printing later.

The code I'm currently running outputs individual documents to a printer.

Code is as follows:

Sub Test()

Dim startrow As Integer, endrow As Integer, rowindex As Integer
Dim msg As String
Dim i As Integer
Dim totalrecords As String
Dim name As String, mark As String, form As String, target As String, positivecomment1 As String, positivecomment2 As String, positivecomment3 As String, progresscomment1 As String, progresscomment2 As String
totalrecords = "counta(Data!A:A)"
Range("H3") = totalrecords


startrow = 2
endrow = InputBox("Enter the last record to merge.")


If startrow > endrow Then
msg = "Error" & vbCrLf & "Silly! Your start row must be smaller than your end row!"
End If


For i = startrow To endrow
 name = Sheets("Data").Cells(i, 1)
 mark = Sheets("Data").Cells(i, 4)
 form = Sheets("Data").Cells(i, 2)
 target = Sheets("Data").Cells(i, 3)
 positivecomment1 = Sheets("Data").Cells(i, 6)
 positivecomment2 = Sheets("Data").Cells(i, 7)
 positivecomment3 = Sheets("Data").Cells(i, 8)
 progresscomment1 = Sheets("Data").Cells(i, 9)
 progresscomment2 = Sheets("Data").Cells(i, 10)
 
 Sheets("Page").Range("B7") = name
 Sheets("Page").Range("g8") = form
 Sheets("Page").Range("b9") = mark
 Sheets("Page").Range("g9") = target
 Sheets("Page").Range("B13") = positivecomment1
 Sheets("Page").Range("B16") = positivecomment2
 Sheets("Page").Range("B19") = positivecomment3
 Sheets("Page").Range("B23") = progresscomment1
 Sheets("Page").Range("B35") = progresscomment2
 
 
 ActiveSheet.PrintPreview
 
 Next i


End Sub

The file can be found here

Please help!

Thanks

Stephen

 

[ranman256]

IF you are printing sheets from 1 workbook then you could try: http://www.novapdf.com/kb/printing-an-entire-excel-workbook-to-a-single-pdf-file-135.html

If you are printing sections from different workbooks, then you must have full Adobe ACROBAT editor,
take control of it , then insert all the external pdf files into one.

 

[sjp1979]

Thanks ranman

I guess I won't be able to do it that way then.

Do you know if they could output to a single word document?

Cheers