Current Page

Norm

unread,

Jun 6, 2002, 11:05:41 AM6/6/02

to

There are times when I just want to print a certain page of a worksheet. I
can do it manually, but how can I do it programmatically if the page I want
to print may change from time to time.

IOW, is there a way to programmatically obtain the current page number of
the active cell?

Ron de Bruin

unread,

Jun 6, 2002, 11:22:21 AM6/6/02

to

Try this

ActiveSheet.Printout From:=1, To:=2

you can change activesheet to a sheet you want.

Regards Ron

"Norm" <ida...@conversent.net> schreef in bericht
news:#i2eEuWDCHA.1940@tkmsftngp04...

Norm

unread,

Jun 6, 2002, 1:55:46 PM6/6/02

to

Thanks Ron, but it wouldn't help much to have to change code every time I
wanted to print a different page. As I stated, I'd like to be able to print
the page of the active cell no matter where the active cell is.

The point is to come up with something that minimizes keystrokes in order to
print just the current page. I want to come up with something akin to the
'Print Current Page' command that exists in Word.

"Ron de Bruin" <ronde...@kabelfoon.nl> wrote in message
news:Og6Mb3WDCHA.2576@tkmsftngp04...

Ron de Bruin

unread,

Jun 6, 2002, 2:08:32 PM6/6/02

to

I search Google for you

try this

Laurent Longre posted the following code on April 21 2000, which
displays a message box with the page number. To print the page, I added
a line at the end of the macro:

Sub PrintCurrentPage()
Dim VPC As Integer, HPC As Integer
Dim VPB As VPageBreak, HPB As HPageBreak
Dim NumPage As Integer

If ActiveSheet.PageSetup.Order = xlDownThenOver Then
HPC = ActiveSheet.HPageBreaks.Count + 1
VPC = 1
Else
VPC = ActiveSheet.VPageBreaks.Count + 1
HPC = 1
End If
NumPage = 1
For Each VPB In ActiveSheet.VPageBreaks
If VPB.Location.Column > ActiveCell.Column Then Exit For
NumPage = NumPage + HPC
Next VPB
For Each HPB In ActiveSheet.HPageBreaks
If HPB.Location.Row > ActiveCell.Row Then Exit For
NumPage = NumPage + VPC
Next HPB
'MsgBox "Page number of the active cell = " & NumPage
ActiveWindow.SelectedSheets.PrintOut _
From:=NumPage, To:=NumPage, _
Copies:=1, Collate:=True

End Sub

"Norm" <ida...@conversent.net> schreef in bericht

news:#DncGNYDCHA.2884@tkmsftngp04...

Norm

unread,

Jun 6, 2002, 2:25:27 PM6/6/02

to

Fantastic! Thanx much for your assistance.

"Ron de Bruin" <ronde...@kabelfoon.nl> wrote in message

news:eKiTSUYDCHA.2212@tkmsftngp02...

Ron de Bruin

unread,

Jun 6, 2002, 2:36:10 PM6/6/02

to

Glad that I good help

<<I added a line at the end of the macro>>

by the way the last line in the macro is from Debra Dalgleish.

Before she got mad at me.(vbg)

Regards Ron

"Norm" <ida...@conversent.net> schreef in bericht

news:#LDLsdYDCHA.1764@tkmsftngp02...

Debra Dalgleish

unread,

Jun 6, 2002, 2:52:10 PM6/6/02

to

Better be careful Ron -- my daughter landed in the Netherlands this
morning. I don't want to have to send her over to talk to you.

Ron de Bruin

unread,

Jun 6, 2002, 2:59:00 PM6/6/02

to

Good I did excuse myself, I now you read them all.

I hope she have a nice time in holland.

Regards Ron

"Debra Dalgleish" <d...@contextures.com> schreef in bericht
news:3CFFAF5A...@contextures.com...

Dana DeLouis

unread,

Jun 6, 2002, 6:46:28 PM6/6/02

to

Just to add as a programming idea only. If you have manually set a Print
Area, this it may not work as intended. The PageBreaks are all different.
This is probably not what you want, but the code to print a specific page
within your Print area is rather different.
As a suggestion, you may want to remember the Print Area if you have set it.
Then remove it prior to running the code. Once you are done printing your 1
page, reset the Print Area.
There are a few ways to do it (just use "PrintArea"). However, here is just
one way that does not involve an additional variable to store the PrintArea.
This is just additional code you may want to add...

Dim CurrentPage As Long
Dim TotalPages As Long

'// Save Settings...
ActiveWorkbook.CustomViews.Add _
ViewName:="_temp", _
PrintSettings:=True, _
RowColSettings:=True

'// Remove Manual PrintArea
ActiveSheet.PageSetup.PrintArea = False

'// Your code to Print here...

'// When done...
'// Reset PrintArea
TotalPages = ExecuteExcel4Macro("Get.Document(50)")

With ActiveWorkbook.CustomViews("_temp")
.Show
.Delete
End With

MsgBox "Printing Page : " & CurrentPage & " out of " & TotalPages

' etc....

--
Dana DeLouis
Windows XP & Office XP
= = = = = = = = = = = = = = = = =

"Ron de Bruin" <ronde...@kabelfoon.nl> wrote in message

news:eDGfujYDCHA.2172@tkmsftngp04...

Dave Peterson

unread,

Jun 6, 2002, 8:27:25 PM6/6/02

to

Ron used to be very nice, proper and polite.

His attitude is getting closer to being Canadian.

Maybe in a couple years, he'll be American (yeah, I know, Canada is in
America--United Statesian???

<VBSEG>

Another one to lookup!!!

--

Dave Peterson
ec3...@msn.com

Paul Watson

unread,

Jun 6, 2002, 8:55:38 PM6/6/02

to

Another problem you might run into is that Excel doesn't continuously update
the page breaks. I don't know when it does, but I've had a lot of problem
with it not having any page breaks when I try to find out where they are. So
you might want to force it to calculate the page breaks first. One way to do
this is to set screenupdating to false, then switch to Page Break View and
back to Normal View. This way you'll be sure that the pagebreaks have been
calculated and are accurate.

Paul

"Dana DeLouis" <ng_...@hotmail.com> wrote in message
news:eEe8mwaDCHA.2164@tkmsftngp02...

Ron de Bruin

unread,

Jun 6, 2002, 11:22:03 PM6/6/02

to

Not in the <FM> Dave

Regards Ron

"Dave Peterson" <ec3...@msn.com> schreef in bericht
news:3CFFFDED...@msn.com...

Jon Peltier

unread,

Jun 7, 2002, 12:33:37 PM6/7/02

to

Dave -

I knew SEG long ago. It wasn't until fairly recently (last year or so?) I
figured out what VBG meant.

- Jon
______

In article <3CFFFDED...@msn.com>, ec3...@msn.com says...

Dave Peterson

unread,

Jun 7, 2002, 7:01:25 PM6/7/02

to

I'm doing my best to corrupt Ron, without being too explicit!

--

Dave Peterson
ec3...@msn.com