Printing a range using a written macro

[nigelandrewfoster]

When I replace the recorded code for reliably printing a particular range with this simplified version, all manner of strange results can occur, usually small printouts or partial printouts. Am I missing something?

Sub DAILY_TASKS()
    With ActiveSheet
        .PageSetup.PrintArea = "$A$1:$u$50"
        .PrintOut Copies:=1, Collate:=True
    End With
End Sub

Thanks!
Nigel

 

[Trevor G]

Is the range a fixed range or selected cells then print them?

2 samples below:

Sub print1()
Range("A1:B25").PrintOut
End Sub
Sub print2()
Selection.PrintOut
End Sub

 

[nigelandrewfoster]

Fixed range only. Beautifully simple solution, my friend!
One problem: it gave me a printout, but it's tiny. I want it to fill the page. When I go into print preview it does just that, but printout is approx 25% the size it should be.

 

[Peter_SSs]

1. If you manually go in to Page Setup, on the Page tab in the Scaling section, what settings do you find?

2. Should A1:U50 fit on a single page? If not do you just want it to use as many pages as it needs?

 

[Trevor G]

What about changing the font size and autofit column before printing then? What is on the current page setup settings?

Sub print1()
Range("J1:N10").Font.Size = 20
Columns("J:N").AutoFit
Range("J1:N10").PrintPreview
End Sub

 

[nigelandrewfoster]

First, Page Tab info: Scaling says Adjust to 100% normal size, Fit to 1 pages wide by 1 tall. Mini printout when former radial button selected, 4-page printout(!) when latter selected.
Second, yes, should fit nicely on a single page. And does do what I want when I manually click "set print area." But I want a simplest possible macro to do the same.

 

[Peter_SSs]

I wouldn't fiddle with your font, column widths etc, but rather just add a little to your original code. Try this:

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> DAILY_TASKS()<br>    <SPAN style="color:#00007F">With</SPAN> ActiveSheet<br>        <SPAN style="color:#00007F">With</SPAN> .PageSetup<br>            .PrintArea = "$A$1:$U$50"<br>            .Zoom = <SPAN style="color:#00007F">False</SPAN><br>            .FitToPagesWide = 1<br>            .FitToPagesTall = 1<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        .PrintOut Copies:=1, Collate:=<SPAN style="color:#00007F">True</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[Trevor G]

Peter's solution will work for you.

 

[nigelandrewfoster]

Thank you both for your help. I'll try your solution in the morning.

 

[nigelandrewfoster]

Perfect. Works a treat. Learned something, too!