Print specific pages of a Database

[Dooley]

I have the following code in my program to allow printing of the
database (size changes frequently).

Public Sub PrintData()
Dim lrow As Range
Dim rc As Integer
Dim ar As Integer
Dim Response As String

Sheets(1).Activate

Set lrow = Sheet1.Range("A" & Rows.Count).End(xlUp)
'
lrow.Activate
'ar = ActiveCell.row
'
'Range(Cells(1, "a"), Cells(ar, "M")).Select ' Selects Print
Area
'
' MsgBox ("Ensure that the Printer is on!")


ActiveSheet.PageSetup.PrintArea = _
ActiveCell.CurrentRegion.Address
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$4"
.PrintTitleColumns = ""
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(0.5)
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLegal
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
Response = MsgBox("Print in Black and White?",
vbYesNo)
If Response = vbYes Then
.BlackAndWhite = True
Else
.BlackAndWhite = False
End If
.Zoom = 100
End With
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Range("A5").Select

Application.ScreenUpdating = True



End Sub


This works beautifully but only prints the entire database, however
there are times that I may only want certain pages to be printed. Any
suggestions on how to do this?

Dooley

 

[Jim Cone]

You can add the number of the starting page and ending page to your code.

From the Excel VBA help file...
"expression.PrintOut(from, To, Copies, Preview, ActivePrinter, PrintToFile, Collate)"

So your code would look like this to print pages 2 and 3...
ActiveWindow.SelectedSheets.PrintOut From:=2, To:=3, Copies:=1, Collate:=True
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"Dooley" <[email protected]>
wrote in message
I have the following code in my program to allow printing of the
database (size changes frequently).

Public Sub PrintData()
Dim lrow As Range
Dim rc As Integer
Dim ar As Integer
Dim Response As String

Sheets(1).Activate

Set lrow = Sheet1.Range("A" & Rows.Count).End(xlUp)
'
lrow.Activate
ActiveSheet.PageSetup.PrintArea = _
ActiveCell.CurrentRegion.Address
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$4"
.PrintTitleColumns = ""
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(0.5)
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLegal
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
Response = MsgBox("Print in Black and White?", vbYesNo)
If Response = vbYes Then
.BlackAndWhite = True
Else
.BlackAndWhite = False
End If
.Zoom = 100
End With
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True '<<<<<
Range("A5").Select
Application.ScreenUpdating = True
End Sub


This works beautifully but only prints the entire database, however
there are times that I may only want certain pages to be printed. Any
suggestions on how to do this?
Dooley

 

[Dooley]

You can add the number of the starting page and ending page to your code.

From the Excel VBA help file...
"expression.PrintOut(from, To, Copies, Preview, ActivePrinter, PrintToFile, Collate)"

So your code would look like this to print pages 2 and 3...
ActiveWindow.SelectedSheets.PrintOut From:=2, To:=3, Copies:=1, Collate:=True

<Snip>

Thanks Jim. As usual I didn't read the help file sufficiently to see
the answer was right in front of my face!

Your help is very much appreciated.

Dooley