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


  • 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
            Selection.ExportAsFixedFormat _
            Type:=xlTypePDF, _
            Filename:=FullName, _
            Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, _
            IgnorePrintAreas:=False, _
        Next i
    End Sub

    Friday, August 14, 2015 3:36 PM