Printing to PDF

[shippey121]

Morning all

i have a document that prints labels, the label has a template sheet and then VBA code cycles through data to populate each label and prints, if i print directly to the printer everything is fine and it prints in order.

what im wanting to do is print to PDF, i can sort the code to export to PDF but it does each print as a separate PDF and im wanting to combine them, i have tried using a PDF printer rather than exporting but it seems to mix them up and not put them in order, i need them in order, the PDF Printer im using is called PDF Creator, after the print finishes i have all the separate files and choose Merge as excel sends each page as a separate print

as you can see the code to print is pretty simple, any suggestions on how i could create a multi page document from 1 sheet

Sub print_labels()
Dim x As Long
Dim y As Long
Dim labels As Integer

On Error GoTo exitsub
x = InputBox("Enter number of Start Pallet")
y = InputBox("Enter number of Pallet Tickets required")
y = y - 1 + x

Sheets("labels").Select
For labels = x To y
Range("F3").Value = labels
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

Next labels

exitsub:

Sheets("Summary Sheet").Select

End Sub

 

[John_w]

This creates a single PDF (named "All Labels.pdf" in the same folder as the macro workbook) with multiple pages, with each label starting on a new page.

Public Sub Print_Labels3()

    Dim s As Variant, n As Variant
    Dim labels As Integer
    Dim PDFworkbook As Workbook
    
    s = InputBox("Enter number of Start Pallet")
    If s = "" Then Exit Sub
    n = InputBox("Enter number of Pallet Tickets required")
    If n = "" Then Exit Sub
    
    Application.ScreenUpdating = False
    
    Set PDFworkbook = Workbooks.Add(xlWBATWorksheet)
    With ThisWorkbook.Worksheets("labels")
        For labels = s To s + n - 1
            .Range("F3").Value = labels
            .Copy After:=PDFworkbook.Worksheets(PDFworkbook.Worksheets.Count)
        Next labels
    End With
    
    With PDFworkbook
        .Worksheets(2).Select
        For n = 3 To Worksheets.Count
            .Worksheets(n).Select False
        Next
        .ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "\All Labels.pdf", _
            Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
        .Close False
    End With

    ThisWorkbook.Worksheets("Summary Sheet").Activate
    
    Application.ScreenUpdating = True
    
End Sub

 

[John_w]

Small correction.

        For n = 3 To Worksheets.Count

should be:

        For n = 3 To .Worksheets.Count

 

[shippey121]

Hi john

worked a treat,i did have to modify one part as it was stuck in and endless loop on this line

For labels = s To s + n - 1

i did sort of find another solution though this evening, using PDF Creator i pressed print and just set it to one page, when the dialog box pops up i choose MERGE, then when setting off the next batch of labels they queued up in the correct order

once finished i just click MERGE and the SAVE

i found your code and approach useful though and could probably use it on some other workbooks that are simliar

thanks again