Select sheets from drop-down & print to PDF

Question

Update: the following VBA snippet is 99% functional and prints the specified sheets for the first division specified in Range("Ref") = Cells(R, "I").

However, when it cycles through again with the next division it doesn't do so correctly and instead blanks out that input so none of the lookups function correctly. It still prints to PDF correctly but without any data in the report. How should I reorganize this code so it will reference the divisions in Range("Ref") = Cells(R, "I") correctly?

Sub TBD()

Dim i As Long, c As Long
Dim SheetArray() As String

    With ActiveSheet.ListBoxSh

        For i = 0 To .ListCount - 1
            If .Selected(i) Then
                ReDim Preserve SheetArray(c)
                SheetArray(c) = .List(i)
                c = c + 1
            End If
        Next i

End With

For R = 2 To 3
Range("Ref") = Cells(R, "I")

ThisWorkbook.Sheets(SheetArray()).Select
                   
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:="[folder path] & Range("D20") & ".pdf", _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=False, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False

Next R

End Sub

I have a worksheet with roughly 24 tabs, of which I'd like to print 5 to PDF with 5 different variations based on a selectable drop-down in cell E7 (i.e., a 5 page report for division 1, division 2, and so on). I have two snippets of VBA code, both functional, that achieve these two functions independently that I am looking to combine.

The image below shows the drop-down that displays all tabs in the workbook and the divisions (1130000, 11320000, etc).

The end-state is intended to be a macro that, when run, will PDF 5 division reports consisting of the 5 selected tabs within my workbook.

I've set up the following code via Leila Gharani whereby an ActiveX ListBox allow the users to print preview the selected sheets, and the Excel Worksheet Activate event to automatically populate the list of sheet names in the List Box.

Leila Gharani Code

Code for the Drop-Down to reflect the names of the tabs in the workbook:

Private Sum Worksheet_Activate()
    Dim Sh
    Me.ListBoxSh.Clear
    For Each Sh In ThisWorkbook.Sheets
    me.ListBoxSh.AddItem Sh.Name
    Next Sh
End Sub

Code to print preview selected tabs:

Sub Print_Sheets()
Dim i As Long, c As Long
Dim SheetArray() As String

    With ActiveSheet.ListBoxSh

        For i = 0 To .ListCount - 1
            If .Selected(i) Then
                ReDim Preserve SheetArray(c)
                SheetArray(c) = .List(i)
                c = c + 1
            End If
        Next i

        Sheets(SheetArray()).PrintPreview

    End With

End Sub

I have also implemented the following macro to cycle through a list of all the report variants I want to PDF based on the division names/values listed in column AC8:AC:13

Sub SavetoPDF()

For r = 8 To 13
Range("E7") = Cells(r, "AC")
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:="Input File Name Here" & Range("AC7") & ".pdf", _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=False, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False

Next r
End Sub

How can I combine these code snippets so the drop-down in E7 will cycle through the division names/values in column AC8:AC:13 and the selected tabs will be printed to PDF accordingly?

Workbook Overview:

Master Data tab: copious amounts of monthly data with a column for division (these are the unique division identifiers that are reflected in cells AC8:AC13)
Control Panel Tab: cell E8 is a data validation drop-down that includes the values in AC8:AC13
Tabs 1 through 5: These tabs pull from the master data tab via SUMIFs that reflect the value in tab: Control Panel, Cell: E8. Any time the division in E8 is changed, this is reflected throughout tabs 1-5.

What do the values in AC8:AC13 look like? How are they translated into the individual sheets which need to be combined for export to PDF? — Tim Williams
@TimWilliams The values in AC8:AC13 are reflected throughout the workbook via a data validation drop-down on the control panel tab. Any change to the drop-down on the master sheet will cascade through the rest of the sheet to reflect the selected division. — ADT
Yes but what do the values in AC8:AC13 represent? Are they sheet names, or something else? And 5 sheets in one PDF, or 5 PDF's? — Tim Williams
I have a master data tab from that every tab pulls data from. There is a column in the master data tab for division - the unique division identifiers in this column are in AC8:AC13. I would like 5 PDFs (1 for each division) with each PDF including 5 sheets. My rationale for including the Gharani macro is so I can easily select/de-select the sheets I'd like to be included in the batch that is PDF'd. — ADT
OK clearly I'm quite dense this evening, but can you explain precisely how to translate the value in (eg) AC8 into a list of 5 sheet names? — Tim Williams

Elio Fernandes Elio Fernandes · Accepted Answer · 2021-08-27T11:39:10

Its' not very easy to understand without seeing principals sheets. Can you create a table like this where you specify the division and the respective sheet tabs to be printed? Then You could just filter the table, create an array of the visible sheets and print them.

Select sheets from drop-down & print to PDF

1 Answers