How to create PDF using a single worksheet in a loop

Question

Current Situation

My Excel workbook contains anywhere from 10 to 32 worksheets used as templates. It also contains 1 worksheet called "Report". I currently loop through all template worksheets and put the information into the Report worksheet, then I create a PDF using that report worksheet.

However this creates 1 PDF per template because it passes through my Report worksheet.

Everytime I send the information to my Report worksheet, that gets turned into a PDF. The problem with that is it generates a PDF everytime my report worksheet gets filled in. I am not exporting my templates as PDF.

---

Code

Here's the code that generates 1 PDF file per template:

Sub CreatePDF()
Dim currentSerialNumber As String 'Worksheet name is the same as the serial number
Dim ws As Worksheet
Dim pdfFilePath As String

'Create a report for each serial number written in the Summary worksheet and export it to PDF
For i = 10 To Rows.Count 'Start at row #10
    If IsEmpty(Worksheets("Summary").Range("B" & i).Value) = False Then
        'Do work
        currentSerialNumber = Worksheets("Summary").Range("B" & i).Value 'Fetches the serial number
        pdfFilePath = "C:\" & currentSerialNumber & ".pdf" 'Ex: C:\1000.pdf
        Worksheets(currentSerialNumber).Activate 'Activate the template for this current serial number
        GenerateReport (currentSerialNumber) 'Put all the info from the template of this serial # into the report worksheet
        Set ws = Worksheets("Reports") 'Set ws object as the report worksheet
        ws.UsedRange.Select
        ws.ExportAsFixedFormat Type:=xlTypePDF, _
                               Filename:=pdfFilePath, _
                               Quality:=xlQualityStandard, _
                               IncludeDocProperties:=True, _
                               IgnorePrintAreas:=False, _
                               OpenAfterPublish:=False
    Else
        'No more serial numbers found, exit this loop
        Exit For
    End If
Next i
End Sub

---

Goal

I want to loop through each template, send the information to my report, add that report information as a page in a PDF file, and repeat until no more templates.

The similar question linked would work if I was printing all my templates. But I'm actually sending my template information to a more professional, sleek looking report Worksheet. The report is then the one that I want into a PDF. The problem is, I only have one report worksheet that gets filled in by multiple templates.

Is this possible using VBA in Excel?

Answer 1

I think you can handle this in a loop. My idea is to do the following:

After copying the data to the "Report" sheet, create a Copy of that sheet in a new workbook. This new workbook will contain each of the "Report" sheets, and then you can export the entire new workbook

I haven't tested it, but let's try something like this:

Sub CreatePDF()
Dim currentSerialNumber As String 'Worksheet name is the same as the serial number
Dim ws As Worksheet
Dim pdfFilePath As String
Dim reports As Workbook

'## Add a new workbook an
Set reports = Workbooks.Add
ThisWorkbook.Activate
Do Until reports.Worksheets.Count = 1
    reports.Worksheets(reports.Worksheets.Count).Delete
Loop

'Create a report for each serial number written in the Summary worksheet and export it to PDF
For i = 10 To Rows.Count 'Start at row #10
    If IsEmpty(Worksheets("Summary").Range("B" & i).Value) = False Then
        'Do work
        currentSerialNumber = Worksheets("Summary").Range("B" & i).Value 'Fetches the serial number
        pdfFilePath = "C:\" & currentSerialNumber & ".pdf" 'Ex: C:\1000.pdf
        Worksheets(currentSerialNumber).Activate 'Activate the template for this current serial number
        GenerateReport (currentSerialNumber) 'Put all the info from the template of this serial # into the report worksheet
        Set ws = Worksheets("Reports") 'Set ws object as the report worksheet
        '## Copy the Reports sheet to the new workbook
        ws.Copy After:=reports.Worksheets(reports.Worksheets.Count)

    Else
        'No more serial numbers found, exit this loop
        Exit For
    End If
Next i

'## There is an empty worksheet in the Reports file, so we can remove it:
reports.Worksheets(1).Delete
'## select all sheets in reports:
reports.Worksheets.Select
'## Export the entire file as fixedformat:
reports.ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
                           Filename:=pdfFilePath, _
                           Quality:=xlQualityStandard, _
                           IncludeDocProperties:=True, _
                           IgnorePrintAreas:=False, _
                           OpenAfterPublish:=False
End Sub