Results 1 to 3 of 3

Thread: Print single PDF using Multiple Worksheets whose names are read from range of cells

  1. #1

    Print single PDF using Multiple Worksheets whose names are read from range of cells



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

    I'm an FORTRAN programmer from way back but I'm new to VB. I have a Workbook which has many worksheets for invoicing. I want to automate creating a single PDF file from multiple worksheets.
    The worksheet names need to vary each time I run the macro. Here is the code. Sorry for all the comments.

    Sub Print_OSARC_PDF()

    'I started out with the following code which has a list of worsheets hard coded in the Sheets line.
    ' These 2 lines with the hard coded worksheet names work but I need to be able to change the worksheets names and order
    ' every time I run the Macro.
    'ThisWorkbook.Sheets(Array("Adams CAD", "Choctaw CAD", "Franklin CAD", "Hinds CAD", "Madison CAD", "Noxubee CAD", "Oktibbeha CAD", "Webster CAD", "Wilkinson CAD", "Yazoo CAD")).Select
    'ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "\" & Myfilename & ".pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, OpenAfterPublish:=True
    'The above 2 lines work fine if all other code is commented out.

    ' To do what I need, I propose to have the code read the Worksheet names from a range of cells (Cells C4:C34)
    ' on a reserved macro sheet (in mine it's called "PRINTPDF") with the code executed from a button on that sheet.

    'Define variable types
    Dim Myfilename As String
    Dim Mylist As String
    Dim Listval As Variant
    Dim msg As String
    Dim sSheets() As String

    'Initialize variables
    Myfilename = "Submit" 'The PDF filename will be Submit.pdf
    Mylist = ""
    msg = " Will print to PDF file the following worksheets: "

    'Populate Listval array with values in cells on macro worksheet
    Listval = Range("C4:C34").Value
    'Build a text string called "Mylist" of worksheet names separated by commas
    'to be passed along to the pdf print routine.
    For i = LBound(Listval) To UBound(Listval)
    'MsgBox Listval(i, 1)
    If Listval(i, 1) <> "" Then
    Mylist = Mylist & Listval(i, 1) & ","
    End If
    Next

    'Build a text string to display in the message box
    msg = msg & vbCr & Mylist
    MsgBox msg
    ' This next line of code did not work
    'ThisWorkbook.Sheets(Mylist).Select

    ' So I next tried to use the Split command in a String array called sSheets.
    sSheets() = Split(Mylist, ",")
    ' I turned on Watches in the Developer window for sSheets and they load with the correct values

    ' My logic below to try and use the Sheets command with .Select does not work
    'Sheets(sSheets()).Select

    ' If either of the above would have worked, I would have try to pass it to the 2 lines below using "Selection" instead of "ActiveSheet" like the code at the top
    'Selection.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "\" & Myfilename & ".pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, OpenAfterPublish:=True

    End Sub

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,356
    Articles
    0
    Excel Version
    365
    if all the cells in the range had a valid sheet name then this would have done:
    Code:
    Sub blah()
    Sheets(Application.Transpose(Range("C4:C34"))).Select
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "\Submit.pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
    End Sub
    but since you're also checking or blanks in the range then, as long as they're all valid sheet names this should do it:
    Code:
    Sub blah2()
    For Each cll In Range("C4:C34").SpecialCells(xlCellTypeConstants, 2).Cells
      If FirstSheetSelected Then
        Sheets(cll.Value).Select False
      Else
        Sheets(cll.Value).Select
        FirstSheetSelected = True
      End If
    Next cll
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "\Submitf.pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
    End Sub
    It assumes the sheet names are plain text (not the result of a formula) and the other cells are truly blank. If these do not pertain, then come back and I'll offer a tweak. I can also offer a tweak if you want the names of the sheets checking to see that they are valid (exist).

  3. #3
    WOW. Thanks for the fast reply. This forum rocks! (Or as they used to say in my day "Groovy Man!) Anyway the second arrangement of code "Sub blah2" works perfectly for my needs. This really will save us all a lot of time. Thank you so much for the help.

Posting Permissions

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