none
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
                sheetsArray.Select
            ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
            Filename:="*location, purposely removed*" & stNames(i) & ".pdf", _
            OpenAfterPublish:=False
             
                
            Next i
            
            
        End Sub
        
        

    Tuesday, August 18, 2015 4:13 PM

Answers

  • 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), _
             OpenAfterPublish:=False
                For j = 1 To sheetsArray.Count
                Debug.Print sheetsArray(j).Name
                    sheetsArray(j).Select
                    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
                    Filename:=ThisWorkbook.Path & "\Report for " & sheetsArray(j).Name, _
                    OpenAfterPublish:=False
                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
    https://social.msdn.microsoft.com/Forums/office/en-us/86682df0-e1d9-4966-ba58-a7f0ee35df73/creating-looping-print-to-pdf-button?forum=exceldev

    Best Regards,

    Edward


    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