How to print multiple named ranges on multiple sheets using VBA?

[msb1977]

Hi all - after reading through many posts I decided to register and post this question because I never could adapt other people's stuff to my work. I'm definitely a beginner with VBA! Anyways, I'll try to explain what I'm trying to accomplish below. Any help would be greatly appreciated.

sheet 1 contains named ranges A and B
sheet 2 contains named ranges C and D

If it matters, ranges A and B have the same # of columns but different # of rows and the same for ranges C and D.

I want to print all to 1 .pdf in a specific order: sheet 1, range A; sheet 2, range C; sheet 1, range B; and sheet 2, range D.

To complicate matters, I need page breaks between all 4 ranges and also range C needs an internal page break and range D needs an internal page break (both at specific rows). Ends up being a total of 6 pages when printed.

As far as print set-up is concerned:
Landscape
Margins: left/right 0.25, top/bottom 0.5, center horizontally
Header: "text" upper left, "text" middle, date on the right
Footer: file name bottom left, "text" middle, page number bottom right

Could someone please help point me in the right direction? Again, any help is greatly appreciated.

Side note: Over the past week I have taught myself (by reading this forum) how to write the VBA code to open a file off a server, filter a table on multiple columns for multiple criteria, copy visible cells only, and paste that into a different workbook...as well as copy the formulas on that worksheet to match the # of rows copied over.

Thanks to you all for allowing me to take bits and pieces of your work and put it into mine.

 

[msb1977]

Cool! Glad you were able to work it out. You write decent code for a self-proclaimed noob.

A couple of thoughts for what it's worth:

If you want the date in the PDF filename, use the FORMAT command to convert the date to a string then concatenate it within the filename.
Filename:="C:\Users\abc\Desktop\Status of Funds (as of " & Format(Date "mm-dd-yyyy") & ").pdf", _

If you want to paste the original column widths
Sheets("PrintSOF").Range("A1").PasteSpecial xlPasteColumnWidths
Or Autofit column widths (after pasting data and changing format)
Columns("A:W").Autofit


There's no "Solved" button for this forum. Just state it's solved and that's it.

Thanks...I actually wrote my first VBA code on Monday (of this week). I'm very good with Excel, just never ventured much into VBA with the exception of copying someone else's code from google to automate number formats for pivot tables.

I figured all of this out with google, this thread, trial and error, and a little bit of luck. I was surprised how much more complicated the printing subroutine is compared to the stuff I did earlier this week.

Have a great rest of the weekend!