Dynamic range for Print to PDF RRS feed

  • Question

  • Hi there, 

    I have a spreadsheet with 15 individual investors' reports, all of which need to be individually packaged along with 7 other accompanying docs (all in the same spreadsheet) and printed to PDF. So basically, the end product should have 15 PDFs each with 8 pages (the first page in each PDF will be the unique investor report). Any help would be greatly appreciated. This is what I have so far:

     Sub Groupssheets()
        Dim sheetsArray As Sheets
        Set sheetsArray = ActiveWorkbook.Sheets(Array("Market Overview, etc.", "PFI Total", _
        "Multiples and Valuations", "Asset Summary", "Debt Info", "Assets and Liabilities", _
        "Stmt. of Operations"))
            Dim stNames(1 To 15) As String
            Dim i As Integer
            stNames(1) = "Investor 1"
            stNames(2) = "Investor 2"
            stNames(3) = "Investor 3"
            stNames(4) = "Investor 4"
            stNames(5) = "Investor 5"
            stNames(6) = "Investor 6"
            stNames(7) = "Investor 7"
            stNames(8) = "Investor 8"
            stNames(9) = "Investor 9"
            stNames(10) = "Investor 10"
            stNames(11) = "Investor 11"
            stNames(12) = "Investor 12"
            stNames(13) = "Investor 13"
            stNames(14) = "Investor 14"
            stNames(15) = "Investor 15"
            For i = 1 To 15
                Worksheets(stNames(i)).Select Replace:=False
            ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
            Filename:="*location, purposely removed*" & stNames(i) & ".pdf", _
            Next i
        End Sub

    Tuesday, August 18, 2015 4:13 PM


  • Hi Connor,

    Could you share us where the code did not work or did you get any error? Based on the code, did it not print the “Worksheets(stNames(i))”? If so, I think you need to save the activesheet as pdf before you select other sheets. A simple code as below:

    Sub ToPdf()
     Dim sheetsArray As Sheets
         Set sheetsArray = ActiveWorkbook.Sheets(Array("Sheet3", "Sheet4"))
     Dim stNames(1 To 2) As String
     Dim i As Integer
     stNames(1) = "Sheet1"
     stNames(2) = "Sheet2"
    For i = 1 To 2
             Worksheets(stNames(i)).Select Replace:=False
             ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
             Filename:=ThisWorkbook.Path & "\Report for " & stNames(i), _
                For j = 1 To sheetsArray.Count
                Debug.Print sheetsArray(j).Name
                    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
                    Filename:=ThisWorkbook.Path & "\Report for " & sheetsArray(j).Name, _
                Next j
    Next i
    End Sub

    In addition, here is a thread you post before, are they the same issue? If they are, did you need us help you merge them? If they are not, did your original issue has been resolved? If it has been resolved, I suggest you mark the answer. If your original issue has not been resolved, please feel free to let us know, and keep following the original post.
    # Creating looping print to pdf button

    Best Regards,


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, August 19, 2015 6:10 AM