How to automatically select specific sheets which belongs to each item of a list and export them to a pdf file

Question

I have the following problem to solve within Excel. I am absolutely not a pro with VBA. But I like it to discover programming with VBA and to solve the problem to make things easier. And surely there will be a better way to set things up, but this is what I have.

What I have I have an excel workbook with several sheets. "Data", "Calc", "Frontpage", "TemplateA", "TemplateB", etc....

• Data sheet has a lot of personal data about members of a test group.
• Calc sheet does some calculations needed to create personal outcomes.
• Frontpage sheet includes some personal data and is used to make a report more 'pro'.
• TemplateA and TemplateB sheets are 'templates' for different reports. These Templates will be used to create individual reports of each member.

What I have done

1. Before I run the macro, I select the templates (Sheets) from which I need to create the reports.

• Next, the macro (Sub Make_Reports_sheets) loops through the list of the members and creates the reports. For every report a new sheet is created with the personal data of each member. So, for each member one or more reports (sheets) can be created according of the number of templates I have selected. The sheets are named: Member1_ReportA, Member1_ReportB, Member1_ReportC, Member2_ReportA, Member2_ReportB, etc.

2. I already have a macro to create a pdf file. But I still mis a piece of code to achieve my goal.

What I want to achieve

• When the individual member reports are created I want to export them to a pdf file. Each pdf files contains the reports which belongs to a member. A front page must me included.

What I need to solve is the following

• How to automatically select the sheets after I created the reports. Put a Front page in front of it and then export this selection to a pdf. And then continue to the next member and do the same again, etc...

Has anybody a solution how to solve this? Thanks a lot! The most relevant peace of code is here

Sub Create_Report_Sheets()
    
    Dim SelectSheet As Object
    Dim ws As Worksheet
    Dim NewSheet As Worksheet   
    
    Dim ListOfNames As Range 'List can contain more than 50 members
    Dim Cell As Range
    
    Set Select.Sheet = ActiveWindow.SelectedSheets
    Set ListOfNames = DataSheet.Range(A1:A & LastRow)
    ...
    For Each Cell In ListOfNames
     For Each ws In SelectSheet
      ws.Copy After:=Sheets(Sheets.Count)
      Set NewSheet = ActiveSheet
      With NewSheet
       .Name = Cell.Offset(0,1) & "ReportA"
       'Do some stuff with this newsheet
      End With
     Next ws
    Next cell
End Sub
    
Sub Make_PDF()
    
    Dim ws As Worksheet
    Dim wb As Workbook
    Dim Frontpage As Worksheet
    Dim SelectSheet As Object
    Dim PathFile As String
    
    Set Frontpage = ThisWorkbook.Sheets("FrontPage")
    ...
    'How to automatically select the reports (sheets) that belongs to a member?
    'How to include a Frontpage as first page of the pdf file?
    ....
    With ws
     .Select
     .ExportAsFixedFormat _
     .Type:=xlTypePDF, _
     .FileName:=PathFile
    End With
    
End Sub

Do I have to do something with a SheetArray and ReDim? Do I have to combine my two piece of code together and handle it all at once?

Answer 1

An array definitely felt needed here so your assumption was correct :)

I hope I didn't make any mistakes when changing from my testSub back to yours:

Sub Create_Report_Sheets()
    
    Dim SelectSheet As Object
    Dim ws As Worksheet, DataSheet As Worksheet
    Dim NewSheet As Worksheet
    Dim LastRow As Range
    Dim ListOfNames As Range 'List can contain more than 50 members
    Dim ccell As Range 'try not to use names used by excel/VBA as variable names
    Dim arrSheets As Variant
    Dim shAmount As Long
    Dim pathFile As String
    
    Set DataSheet = ThisWorkbook.Sheets("DataSheet")
    LastRow = DataSheet.Range("A" & Rows.Count).End(xlUp).Row 'fill your LastRow variable before using it
    Set SelectSheet = ActiveWindow.SelectedSheets 'Select.Sheet would throw an error here
    Set ListOfNames = DataSheet.Range("A1:A" & LastRow) 'don't forget your " with "A1:A"
    For Each ccell In ListOfNames
        shAmount = 1
        ReDim arrSheets(shAmount To shAmount)
        arrSheets(shAmount) = "FrontPage" 'put frontpage as your first element of your array
        For Each ws In SelectSheet
            ws.Copy After:=Sheets(Sheets.Count)
            Set NewSheet = ActiveSheet
            With NewSheet
             .Name = ccell.Offset(0, 1) & ws.Name 'if the names are in the A-column, why the offset? Probably also should use ws.Name instead of "ReportA"
             shAmount = shAmount + 1
             ReDim Preserve arrSheets(1 To shAmount) 'make Array bigger
             arrSheets(shAmount) = .Name
             'Do some stuff with this newsheet
            End With
        Next ws
        ThisWorkbook.Sheets(arrSheets).Select
        pathFile = ThisWorkbook.Path & " " & ccell.Offset(0, 1).Value2 & " Reports - " & Format(Now, "DD.MM.YYYY") & ".pdf"
         'adjust pathFile where needed
'~~~~ This will save over existing files with same name
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, fileName:= _
            pathFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, _
            IgnorePrintAreas:=False, OpenAfterPublish:=False
        Application.DisplayAlerts = False
 '       For i = 2 To UBound(arrSheets) 'uncomment the for loop if you want to delete the sheets after using them for the pdf file
 '       Sheets(arrSheets(i)).Delete
 '       Next i
        Application.DisplayAlerts = True
    Next ccell
End Sub

If you have any more questions, feel free to ask. Also: check this post out for a different approach: https://stackoverflow.com/a/36107539/19353309