Grouping together Sheets to create multiple pdfs

[Etv5002]

I have a book with regional managers listed horizontally on row 2. Currently there are 15 managers so there are 15 columns, one for each one of them.

under each managers name is a list of their stores, the name of the stores is the exact same as the individual stores sheet name.

Ie.
Dave
Store 2
Store 6
Stor2 67

I would like to group the individual sheets into PDFs so they can be distributed to the Managers.

Currently I'm facing an issue with the array, if a cell is blank then the array errors out. All the managers have different numbers of stores ranging from 2-16 each.

Sub SToPDF()

Dim OriginalSheet As String
Dim Sh1 As String
Dim Sh2 As String
'Dim Sh3 As String
'Dim Sh4 As String
'Dim Sh5 As String
'Dim Sh6 As String
'Dim Sh7 As String
'Dim Sh8 As String
'Dim Sh9 As String
'Dim Sh10 As String
'Dim Sh11 As String
'Dim Sh12 As String
'Dim Sh13 As String
'Dim Sh14 As String
'Dim Sh15 As String


    Sh1 = Sheets("Dp List").Cells(4, 2).Value
    Sh2 = Sheets("Dp List").Cells(5, 2).Value
'    Sh3 = Sheets("Dp List").Cells(6, 2).Value
'    Sh4 = Sheets("Dp List").Cells(7, 2).Value
'    Sh5 = Sheets("Dp List").Cells(8, 2).Value
'    Sh6 = Sheets("Dp List").Cells(9, 2).Value
'    Sh7 = Sheets("Dp List").Cells(10, 2).Value
'    Sh8 = Sheets("Dp List").Cells(11, 2).Value
'    Sh9 = Sheets("Dp List").Cells(12, 2).Value
'    Sh10 = Sheets("Dp List").Cells(13, 2).Value
'    Sh11 = Sheets("Dp List").Cells(14, 2).Value
'    Sh12 = Sheets("Dp List").Cells(15, 2).Value
'    Sh13 = Sheets("Dp List").Cells(16, 2).Value
'    Sh14 = Sheets("Dp List").Cells(17, 2).Value
'    Sh15 = Sheets("Dp List").Cells(18, 2).Value
    
    Sheets(Array(Sh1, Sh2)).Select
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:\Users\evanderheof\Desktop\" & Sheets("Dp List").Range("b2").Value & ".pdf"


    'Reset location
    Sheets("Dp List").Select


End Sub

 

[Etv5002]

bump, anything?