VBA to print specific pages

[aggiemarine07]

Howdy all! I have a question for the general audience about trying to print specific pages in my workbook. I've found a few places on the internet that pointed me in the right direction but I think my VBA code is a little in-efficient. It gets the job done but it does it in a slightly annoying way.

What I am trying to do is print out pages 162 to 169 of my workbook, these pages are used in a presentation to my boss once a quarter. Pages 162, 163, 167, 168, 169 are static one page documents. However pages 164, 165, and 166 are dynamic. On these pages it lists the number of drops we've had from our training program. These drops can be as short as half a page and sometimes the full three pages.

I put together some VBA that allows me to print the static pages and the dyanmic pages (based on a value in O2) easily but when I run my macro it prints it into three seperate PDF documents. I want it to print into one document but I cant seem to figure out how. Here is the code:



Sub Print_Specific_Pages()


ThisWorkbook.PrintOut From:=162, To:=163
ThisWorkbook.PrintOut From:=167, To:=169


If Worksheets("EOC p3 Line Drops").Range("O2").Value < 50 Then
ThisWorkbook.PrintOut From:=164, To:=164


ElseIf Worksheets("EOC p3 Line Drops").Range("O2").Value < 101 Then
ThisWorkbook.PrintOut From:=164, To:=165

ElseIf Worksheets("EOC p3 Line Drops").Range("O2").Value < 152 Then
ThisWorkbook.PrintOut From:=164, To:=166

End If


End Sub

How can I make it better? Thanks in advance!

 

[sijpie]

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> Print_Specific_Pages()<br><SPAN style="color:#007F00">' Print selected pages depending on number of rows on a sheet</SPAN><br><br>    <SPAN style="color:#00007F">Dim</SPAN> sPages2Print <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <br>    sPages2Print = "162-"<br>    <br>    <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> Worksheets("EOC p3 Line Drops").Range("O2").Value<br>        <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Is</SPAN> < 50<br>            <SPAN style="color:#007F00">' add page 164</SPAN><br>            sPages2Print = sPages2Print & "164"<br>        <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Is</SPAN> < 101<br>            <SPAN style="color:#007F00">' add 165</SPAN><br>            sPages2Print = sPages2Print & "165"<br>        <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Is</SPAN> < 152<br>            <SPAN style="color:#007F00">' add 166</SPAN><br>            sPages2Print = sPages2Print & "166"<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN><br>    <SPAN style="color:#007F00">' now add last report pages</SPAN><br>    sPages2Print = sPages2Print & ",167-169"<br>    <br>    Application.PrintOut Filename:="", Copies:=1, Range:=wdPrintRangeOfPages, Pages:=sPages2Print<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[aggiemarine07]

I think you code is designed only for Microsoft Word, based on the Application.PrintOut code. When I run it I get an error 438 "object doesnt support property". I did some more research over the weekend and found my answer using this:

Sub Print_Specific_Pages()




If Worksheets("EOC p3 Line Drops").Range("O2").Value < 50 Then
Sheets("EOC p3 Line Drops").Select
ActiveSheet.PageSetup.PrintArea = "$B$2:$N$52"


Sheets(Array("EOC p1 Cover Page", "EOC p2 Overview", "EOC p3 Line Drops", "EOC p4 Performance", "EOC p5 Grad Summary", "EOC p6 Personnel")).Select


ActiveWindow.SelectedSheets.PrintPreview
Sheets("EOC p1 Cover Page").Select 'Needed so that multiple sheets don't remain selected




ElseIf Worksheets("EOC p3 Line Drops").Range("O2").Value < 101 Then
Sheets("EOC p3 Line Drops").Select
ActiveSheet.PageSetup.PrintArea = "$B$2:$N$101"


Sheets(Array("EOC p1 Cover Page", "EOC p2 Overview", "EOC p3 Line Drops", "EOC p4 Performance", "EOC p5 Grad Summary", "EOC p6 Personnel")).Select


ActiveWindow.SelectedSheets.PrintPreview
Sheets("EOC p1 Cover Page").Select 'Needed so that multiple sheets don't remain selected






ElseIf Worksheets("EOC p3 Line Drops").Range("O2").Value < 152 Then
Sheets("EOC p3 Line Drops").Select
ActiveSheet.PageSetup.PrintArea = "$B$2:$N$154"


Sheets(Array("EOC p1 Cover Page", "EOC p2 Overview", "EOC p3 Line Drops", "EOC p4 Performance", "EOC p5 Grad Summary", "EOC p6 Personnel")).Select


ActiveWindow.SelectedSheets.PrintPreview
Sheets("EOC p1 Cover Page").Select 'Needed so that multiple sheets don't remain selected


End If

It's not as neat or refined as yours is but it worked for me. Thanks for all your efforts in trying to help me!

 

[sijpie]

The following code is exactly the same as yours, but a bit more compact without repetition.

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> Print_Specific_Pages()<br>    <SPAN style="color:#007F00">' adjust the print range on EOC p3 Line Drops _<br>      according to number</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Worksheets("EOC p3 Line Drops")<br>        <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> .Range("O2").Value<br>            <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Is</SPAN> < 50<br>                .PageSetup.PrintArea = "$B$2:$N$52"<br>            <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Is</SPAN> < 101<br>                .PageSetup.PrintArea = "$B$2:$N$101"<br>            <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Is</SPAN> < 152<br>                .PageSetup.PrintArea = "$B$2:$N$154"<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#007F00">' now print out all sheets</SPAN><br>    Sheets(Array("EOC p1 Cover Page", "EOC p2 Overview", _<br>            "EOC p3 Line Drops", "EOC p4 Performance", _<br>            "EOC p5 Grad Summary", "EOC p6 Personnel")).PrintPreview<br>    <br>    Sheets("EOC p1 Cover Page").Select <SPAN style="color:#007F00">'Needed so that multiple sheets don't remain selected</SPAN><br><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><SPAN style="color:#00007F">Sub</SPAN> PrintSheets(Preview <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>, ParamArray SheetNames() <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>)</FONT>


If you have something like in your case:

sheets("Sheet1").Select
Activesheet.Pagesetup

or

Range("A3:N40").Select
Selection.ClearContents

then you can skip the select part (which is slow) and just put the two lines together

sheets("Sheet1").Pagesetup

and

Range("A3:N40").ClearContents

it reads easier and it is faster to run.

 

[aggiemarine07]

awesome! thanks again!