Results 1 to 2 of 2

Thread: Help with VBA code printing multiple worksheets to a single pdf file

  1. #1

    Help with VBA code printing multiple worksheets to a single pdf file



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello,

    I am a complete newbie with VBA, just started "working" with it a couple of days ago... I have created an excel 2010 file for component information, each component has 3 different sheets to log different data. In total there are 30 components to the assembly so 90 sheets. Depending on 2 selection criteria I want to print the generated data (by the selection) as a single pdf file. After looking around I used the VBA code as below to generate a control sheet in which my selection creteria generates a "X" behind the sheet name in order to print the sheet. Everything works as intended however this code generates multiple pdf files which can add up to 30 different files, not really usefull. I looked at the code as specified in the knowledge base for printing multiple worksheets to a single pdf file, but as a newbie I am a bit puzzled how to intigrate this. Could anybody help me with the integration so my output will be a signgle pdf file? It does not need to be hardwired in name or location, as it works right know is ok if the output would be one file. Thanks for any help!

    Michael Spaan

    Sub Controlsheet()
    Dim i As Integer

    On Error Resume Next 'Delete this sheet if it already exists
    Sheets("Control Sheet").Delete
    On Error GoTo 0

    Sheets.Add 'Add the WhatToPrint Sheet
    ActiveSheet.Name = "Control Sheet"

    Range("A1").Select 'Label the columns
    ActiveCell.FormulaR1C1 = "Sheet Name"

    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Print?"

    Cells.Select
    Selection.Columns.AutoFit

    For i = 1 To ActiveWorkbook.Sheets.Count
    Cells(i + 1, 1).Value = Sheets(i).Name
    Next
    End Sub
    Sub PrintSelectedSheets()
    Dim i As Integer
    i = 2

    Do Until Sheets("Control Sheet").Cells(i, 1).Value = ""
    If Trim(Sheets("Control Sheet").Cells(i, 2).Value <> "") Then
    Sheets(Sheets("Control Sheet").Cells(i, 1).Value).Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:="PDFCreator", _
    Collate:=True
    End If
    i = i + 1
    Loop
    End Sub

    By double clicking on my overview sheet with the code below the pdf print job is started.

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Call PrintSelectedSheets
    End Sub

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,356
    Articles
    0
    Excel Version
    365
    try (untested):
    Code:
    Sub PrintSelectedSheets()
    Dim i As Integer, FirstSheetSelected As Boolean
    i = 2
    Do Until Sheets("Control Sheet").Cells(i, 1).Value = ""
      If Trim(Sheets("Control Sheet").Cells(i, 2).Value <> "") Then
        Sheets(Sheets("Control Sheet").Cells(i, 1).Value).Select IIf(FirstSheetSelected, False, True)
        FirstSheetSelected = True
      End If
      i = i + 1
    Loop
    If FirstSheetSelected Then ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:="PDFCreator", Collate:=True
    End Sub
    Last edited by p45cal; 2014-09-10 at 09:39 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •