Print multiple PDFs from worksheet

Hello - I'm looking for some guidance on how to automate the print to pdf process for a series of periodic reports I have created. The first page and last page of each report are identical. Each is housed on a single tab. The body content of each report is unique and housed on single tabs. The reports are client-facing and it is imperative that they are uniform and of the highest fidelity possible. I have attached an abbreviated version of the report.

My current process for report creation. We'll use the Aerospace & Defense report as an example.

First Page - Do once and splice into all reports after I have finished.

1. Select first tab
2. print to PDF - My print settings are already saved to the workbook so nothing needs to be done here
3. Save as a certain file name

Body Content and Last Page - Loop for all 15 current reports(this number can increase or decrease)

1. Select both Aerospace & Defense tab and last page tab
2. print to PDF - My print settings are already saved to the workbook so nothing needs to be done here
3. Save as a certain file name

I do not print all three sections at once due to how I have the footers set up. I'm open to suggestions if there is a better way to do this.

• First page has no footers
• Body content first page has no footer, all successive pages have footers,
• Last page has no footers

I would love to be able to click one button and have the macro loop through the creation of all necessary reports based on an existing range('Worksheet Key'!C5:C19) of report names. The ultimate goal would be to not have to do anything outside of excel to prepare the reports for publishing but I'd be content with a macro that just creates 15 reports containing only the body content and last page.


The code below is as far as I've gotten. While it does work, it doesn't loop and wouldn't be very efficient.

Please Login or Register  to view this content.

Happy to answer any questions. Any help would be greatly appreciated.