Advice: Print to PDF OR Print to Printer macro

Hi guys,

So I need to EITHER have a macro which prints a worksheet or sends that worksheet to a PDF file. Here's the challenging parts though:

1. Needs to work on Mac & Windows
2. Sheet being printed is different to where the macro button will be
3. Specific print area needs to be set (I figured out SetPrintArea)
4. Page setup converted to Landscape
5. 'Fit to page' set to 1 page wide by 8 pages tall

It's those last 2 that are giving me grief. While PDF would be more elegant, the file save path issue makes it tricky for me. So I went down the printing path but it's not resizing to 1 wide by 8 tall...

Any advice?

Hi Brenton,

I can't help you with the Mac part at all.

Items 4 and 5 are difficult even with ideal conditions. To be successful, you have to understand how VBA printing differs from manual printing.

VBA has no 'fit to page' setting. You need to set the margins including headers and footers (usually preset manually) and then have an appropriate ZOOM Percent until the output looks good. That's probably why you are having problems with the 1 x 8. It is often a tedious trial and error process.

Pdf output is similar, but a little trickier, especially if you are not printing the entire print area. When printing, you can specify a starting an ending page number to print. Pdf requires a range. You have to convert the page numbers to a range for pdf. This is not trivial when the print area is more than one page wide for a number of reasons including, 'page numbers down then over', 'page numbers over then down', possible non-contiguous print areas.

If you can upload a sample file containing typical 1 x 8 sheets, I may be able to provide some code for both printing and pdf files.


Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Lewis

For item 5 perhaps

Please Login or Register  to view this content.

Untested as I've got no printer at the moment.

Alf

Thank you so much, I have been away for a few days and just read this. Shall check it out now!

Here's a sample. The 'Report Dark' tab is the screen version, while 'Report' is the print version.

Does that make sense?