VBA to print specific pages

aggiemarine07

New Member
Joined
Nov 5, 2013
Messages
46
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!
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

sijpie

Well-known Member
Joined
Nov 1, 2008
Messages
3,734
<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

New Member
Joined
Nov 5, 2013
Messages
46
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

Well-known Member
Joined
Nov 1, 2008
Messages
3,734
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:
Code:
sheets("Sheet1").Select
Activesheet.Pagesetup
or
Code:
Range("A3:N40").Select
Selection.ClearContents
then you can skip the select part (which is slow) and just put the two lines together
Code:
sheets("Sheet1").Pagesetup
and
Code:
Range("A3:N40").ClearContents

it reads easier and it is faster to run.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,688
Messages
5,597,546
Members
414,154
Latest member
thevaper

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top