Can't fix the printing area of my worksheet!

**niceguy21** · 02-08-2013, 07:20 AM

the code below is designed to perform two tasks on the attached worksheet. This worksheet is acturally created by another macro so I needed to create a new named range evertime you try and print it.

Tasks:

Establish a named range as: "MARKED_UP_BUDGET"
Establish the rows to repeat at the top $1:$9
Establish print area as "MARKED_UP_BUDGET"
Print a PDF

You will see however that the third step is not dynamic, you cannot set:

Please Login or Register  to view this content.

so instead I have:
ActiveSheet.PageSetup.PrintArea = "B1:L1000" which is not ideal. is there a way to insure that the print area is always set dynamically?

Please Login or Register  to view this content.

**OllieB** · 02-08-2013, 07:29 AM

Try

Please Login or Register  to view this content.

**niceguy21** · 02-08-2013, 08:33 AM

Originally Posted by OllieB

Try

Please Login or Register  to view this content.

This code worked great: Many thanks I'll rep you. One more question though: which part of my code is responsible for forcing the range to fit one page as opposed to the % of the page.... I want it to print over the the amount of pages required whilst maintaining formatting, sizes

EDIT: I guess what I'm trying to say is I cant' find a means of forcing excel to use the option adjust to page instead of fit to page: I don't want it printed all to one page if the sheet is long

because everything shrinks!

**OllieB** · 02-08-2013, 09:08 AM

niceguy21, the two options below can be used to manipulate the output

Please Login or Register  to view this content.

**niceguy21** · 02-08-2013, 09:12 AM

Originally Posted by OllieB

niceguy21, the two options below can be used to manipulate the output

Please Login or Register  to view this content.

What would you suggest doing?

**OllieB** · 02-08-2013, 09:26 AM

niceguy21, it all depends on the size and layout of your document! I cannot see what is happening, how it looks and what would be the most practical way of printing.

I would try setting (Tall=10 pages is just arbitrarily chosen, I think it will ignore a value that is too high anyway):

Please Login or Register  to view this content.

**niceguy21** · 02-08-2013, 11:05 AM

Originally Posted by OllieB

niceguy21, it all depends on the size and layout of your document! I cannot see what is happening, how it looks and what would be the most practical way of printing.

I would try setting (Tall=10 pages is just arbitrarily chosen, I think it will ignore a value that is too high anyway):

Please Login or Register  to view this content.

Okay this is a step forwards for sure! all the pages are being printed.. all the way to page 10 with my desired level of zoom. However, the heading is still carried down 10 sheets.. how do correct this and have a dynamic number of sheets?

Many thanks

**OllieB** · 02-08-2013, 01:48 PM

I am not really sure what you are asking. Is you report longer or shorter than 10 pages? What would be the settings you would use when you print the report manually?