Printing Multiple Ranges with Adjusted Formatting; to Multiple PDF Sheets in a Single PDF Document

[reberryjr]

I'm facing a complicated (to me) issue in printing a worksheet via VBA. The ranges I'd like to print are:

A1:Q63 to PDF page 1
A1:B12 & A65:AK74 to PDF page 2
A1:B12 & A76:AC125 to PDF page 3
*Unfortunately, I cannot upload images from my work laptop.
**I could get rid of A1:B12 on pages 2 & 3 if I figure out how to update each page's header and/or footer with cell values. I haven't looked into this yet.

My immediate questions are:
1. I assume I need to declare each of those 4 different ranges as such; allowing me to use the range name to set print areas. Is this accurate?
2. I would like to adjust the column width of certain columns when printing that range. Example: On page 1, I'd like to adjust the width of columns P & Q to 7, then back to 18 when printing page 3. I assume the code's flow would be something like Set Ranges --> Adjust Formatting --> Set Print Areas --> Print PDF sheet 1 --> Clear Print Areas --> Adjust Formatting --> Set Print Areas --> Print PDF sheet 2 --> Clear Print Areas --> Adjust Formatting --> Set Print Areas --> Print PDF sheet 3 --> Clear Print Areas. I'm struggling to find something on the web that shows how to print all 3 pages outlined above, into a single PDF document. Any suggested links that can help me achieve my goals?

 

[John_w]

Your request is quite complicated because of the need to save the ranges to separate PDF pages and with different widths for the same columns on different pages.

To answer your questions:

1. Yes, you'd need to define the 4 different ranges in the code, but I don't think it's necessary to set the print areas.
2. The steps you describe would result in 3 separate PDF files (if you give each file a different name). You would then need a separate tool (e.g. PDFtk Server) to merge the 3 PDF files to a single PDF.

To achieve the desired output entirely with Excel I would do it this way:

1. Add 3 temporary sheets.
2. Copy and Paste the ranges to the respective temporary sheet and adjust the columns P:Q widths as required. Note: to copy the original cell formats and column widths you might need to do Paste Special instead of Paste. Do Paste Special twice, first with All using Source theme, and second with Column widths, then adjust the columns P:Q widths.
3 Select all 3 temporary sheets (group them).
4. Save As the 3 active sheets as a PDF.
5. Delete the 3 grouped temporary sheets.

Do all the above steps with the macro recorder and it should give you a single macro which creates the PDF with the desired layout.

 

[reberryjr]

Your request is quite complicated because of the need to save the ranges to separate PDF pages and with different widths for the same columns on different pages.

To answer your questions:

1. Yes, you'd need to define the 4 different ranges in the code, but I don't think it's necessary to set the print areas.
2. The steps you describe would result in 3 separate PDF files (if you give each file a different name). You would then need a separate tool (e.g. PDFtk Server) to merge the 3 PDF files to a single PDF.

To achieve the desired output entirely with Excel I would do it this way:

1. Add 3 temporary sheets.
2. Copy and Paste the ranges to the respective temporary sheet and adjust the columns P:Q widths as required. Note: to copy the original cell formats and column widths you might need to do Paste Special instead of Paste. Do Paste Special twice, first with All using Source theme, and second with Column widths, then adjust the columns P:Q widths.
3 Select all 3 temporary sheets (group them).
4. Save As the 3 active sheets as a PDF.
5. Delete the 3 grouped temporary sheets.

Do all the above steps with the macro recorder and it should give you a single macro which creates the PDF with the desired layout.

@John_w thanks for the reply. Funny enough, this is basically the route I was thinking I would need to go. It's more similar to things I've done in the past, but I was curious if there was a way to be more "sophisticated". LOL. Again, thanks for the reply!