none
Creating looping print to pdf button RRS feed

  • Question

  • Hi there, 

    I need help making a print to PDF button for an investors' report. We have 16 individual investors that each need to receive their own unique sheet. But in addition to each receiving their own report sheet, they also all need to each receive six complimenting sheets as well. Does this make sense? Basically, all 16 people need to have printed for them their own unique report with the 6 general sheets as well. 

    Thank you in advance. 

    Friday, August 14, 2015 2:11 PM

Answers

  • Try code like this, which assumes that the individual sheets are sheets 1 to 16, and the 6 complimenting sheets are sheets 17 to 22, and uses the sheet names of the individual sheets to make the file names.

    Sub MakePDFReports()
        Dim wsNames(1 To 7) As String
        Dim FullName As String
        Dim i As Integer
     
        For i = 1 To 16
        
            wsNames(1) = Worksheets(i).Name
            wsNames(2) = Worksheets(17).Name
            wsNames(3) = Worksheets(18).Name
            wsNames(4) = Worksheets(19).Name
            wsNames(5) = Worksheets(20).Name
            wsNames(6) = Worksheets(21).Name
            wsNames(7) = Worksheets(22).Name
        
            FullName = ThisWorkbook.Path & "\Report for " & Worksheets(i).Name
            
            Worksheets(wsNames).Select
        
            Selection.ExportAsFixedFormat _
            Type:=xlTypePDF, _
            Filename:=FullName, _
            Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, _
            IgnorePrintAreas:=False, _
            OpenAfterPublish:=False
        
        Next i
        
    End Sub

    Friday, August 14, 2015 3:36 PM