Print Selection To Fit Page, but also need printer dialogue pop up.

[dazbear]

Pretty much a VBA novice, so hope you all can help

I have a multi-sheet workbook and want to add a print button to a worksheet which shows a report. This involves selection and fit to page.

However as there are a number of printers in the building I need the printer dialogue to pop up.

I have cobbled together the code below so far, which seems to print the required selection......however it does not fit to page. Any ideas?

Thanks D

Please Login or Register  to view this content.

[Zer0Cool]

Maybe try

Please Login or Register  to view this content.

instead. Also put it outside the With statement (shouldnt matter, but its not part of the with anyway).

Fit pages is what you want, 1x1 which is what you have.

Why do you have titlerows and title columns set that way? As it you have it set to print rows 18:69 on every sheet and columns A:O on every sheet as if they are headers. Try commenting those out and see if it works. Since you are printing a single page you dont benefit from setting repeating headers any way.

[dazbear]

I want it to print selection A18:O69 of the current worksheet, landscape and to fit on 1 page.

What I get it this, with it small in the middle, rather than nice and big on a page.

Excel.jpg

[Zer0Cool]

When you tell Excel to print 1 wide by 1 tall you are telling it to fit the print area within a single sheet. Rowtitles and columntitle do not accomplish that, they only tell Excel to repeat those rows/columns on every printed sheet. You want the pagesetup.printarea property. See this link. That will let you set the printable area that will then get fit into 1x1.