Excel macro to select certain worksheets and print to one pdf

Question

I have an excel workbook in excel 2007 which has approx 110 separate worksheets.

I want to have a menu page where the user can say yes or no to the title of the worksheet they want and press a button to run a macro which will then select the worksheets which the user has said Y too and then print them to PDF as one single PDF and not loads of individual PDFs.

I currently have the following code which selects the worksheets and prints them. At present though when I select a PDF printer it prints but only to multiple PDFs and not one single PDF.

Sub Printselection()
Dim rng As Range
Dim wks As Worksheet
For Each rng In Sheets("RA Database").Range("Q6:Q119")
    If Trim(rng.Value) <> "" Then
        On Error Resume Next
        Set wks = Nothing
        Set wks = Sheets(rng.Value)
        On Error GoTo 0
        If wks Is Nothing Then
            MsgBox "Sheet " & rng.Value & " does not exist"
        Else
             Application.Dialogs(xlDialogPrinterSetup).Show
             wks.PrintOut
        End If
    End If
Next rng
End Sub

The hard copy I'm happy for it to work like this but we need for the PDF copy to collate.

I'm a VB newbie so any help really would be appreciated!!

I did see that original post but that selects all of the worksheets where as this one references a column and then only prints those worksheets which match entries within the list — David Goodwin, Feb 17 '15 at 15:04

Jeanno · Accepted Answer · 2015-02-17T16:21:14.977

In order to print all the sheets into on PDF file, you will need to store all the sheet names you want to print into an array and then use the following command.

Worksheets(printSheets).PrintOut Preview:=False, ActivePrinter:="Adobe PDF", PrintToFile:=True, PrToFileName:=PSFileName

where printSheets is the array holding the names of sheets you want to print

EDIT:

This will work for you

Sub Printselection()
    Dim rng As Range
    Dim wks As Worksheet
    Dim arr() As String
    Dim i As Long: i = 0
    For Each rng In Sheets("RA Database").Range("Q6:Q119")
        If Trim(rng.Value) <> "" Then
            On Error Resume Next
            Set wks = Nothing
            Set wks = Sheets(rng.Value)
            On Error GoTo 0
            If wks Is Nothing Then
                MsgBox "Sheet " & rng.Value & " does not exist"
            Else
                ReDim Preserve arr(i)
                arr(i) = wks.Name
                i = i + 1
            End If
        End If
    Next rng
    Dim printSheets As Variant
    printSheets = arr
    Worksheets(printSheets).PrintOut Preview:=False, ActivePrinter:="Adobe PDF", PrintToFile:=True, PrToFileName:=PSFileName
End Sub

Will that collate only the sheets that have been collated by the code i originally posted? To my very green eye that would print but each as an individual pdf and not as a single PDF with all of the worksheets compiled within it. Please correct me if I am well and turly wrong which i probably am lol — David Goodwin, Feb 17 '15 at 14:59
Sorry for the delay in coming back!! Jeanno you are an absolute star!! Works perfectly. Thank you so much!! :D — David Goodwin, Mar 05 '15 at 09:37

Excel macro to select certain worksheets and print to one pdf

1 Answers1