none
How to select specify multiple sheets to make into a PDF? RRS feed

  • Question

  • I am trying to use some code found here --> http://msdn.microsoft.com/en-us/library/ee834871(office.11).aspx.  I am specifically looking at the section "To create a PDF file of the active sheet or selected worksheets".  I am trying to figure out how to modify the code below to specify what sheets I want to make into a PDF.  I see you can select a single sheet but how do I modify it so select just the sheets I specify?

    I thought it might be something like FileName = RDB_Create_PDF(Sheet(array("Sheet1", "Sheet2", "", True, True))) but this gives an error.  Any help would be appreciated.

    Thanks

     'Call the function with the correct arguments.
        'You can also use Sheets("Sheet3") instead of ActiveSheet in the code(the sheet does not need to be active then).
        FileName = RDB_Create_PDF(ActiveSheet, "", True, True)

    Sub RDB_Worksheet_Or_Worksheets_To_PDF()
        Dim FileName As String

        If ActiveWindow.SelectedSheets.Count > 1 Then
            MsgBox "There is more than one sheet selected," & vbNewLine & _
                   "and every selected sheet will be published."
        End If

        'Call the function with the correct arguments.
        'You can also use Sheets("Sheet3") instead of ActiveSheet in the code(the sheet does not need to be active then).
        FileName = RDB_Create_PDF(ActiveSheet, "", True, True)

        'For a fixed file name and to overwrite it each time you run the macro, use the following statement.
        'RDB_Create_PDF(ActiveSheet, "C:\Users\Ron\Test\YourPdfFile.pdf", True, True)

        If FileName <> "" Then
            'Uncomment the following statement if you want to send the PDF by e-mail.
            'RDB_Mail_PDF_Outlook FileName, "ron@debruin.nl", "This is the subject", _
               "See the attached PDF file with the last figures" _
              & vbNewLine & vbNewLine & "Regards Ron de bruin", False
        Else
            MsgBox "It is not possible to create the PDF; possible reasons:" & vbNewLine & _
                   "Add-in is not installed" & vbNewLine & _
                   "You canceled the GetSaveAsFilename dialog" & vbNewLine & _
                   "The path to save the file is not correct" & vbNewLine & _
                   "PDF file exists and you canceled overwriting it."
        End If
    End Sub

    Thursday, July 24, 2014 3:00 PM

Answers

  • As usual, the answer to many VBA questions is to record a macro of you manually doing teh task.

    So I clicked on the first sheet, then held Ctrl down and clicked on another sheet and got teh recorded macro:

        Sheets(Array("Sheet1", "Sheet3")).Select
        Sheets("Sheet1").Activate

    With these sheets selected, printing and outputting as pdf only uses selected sheets.


    Rod Gill
    Author of the one and only Project VBA Book
    www.project-systems.co.nz

    • Marked as answer by Brutter Monday, July 28, 2014 1:43 PM
    Sunday, July 27, 2014 3:19 AM