Need Macro to print curent page only

[Don Lowe]

I have many people using the file. Each person only needs a page worth of
information. I need a Macro to print curent page only.

 

[JLGWhiz]

Not sure about the current page, but in the PrintOut arguments you can
specify the From and To page numbers, so if your current page is 3 the
From:=3. To:=3 should do it.

 

[Don Lowe]

I am hoping to have a macro that looks at what pape I am on and will print
the specific page. This way I do not need to make 97 buttons for printing.
One button printing where ever you are (most of my users would not now where
they are and I am trying to make this really really easy for them).

 

[Susan]

i know what you want - Word has "print current page" & i've often
wished Excel had the same thing.
i've done it on a small scale by hard-coding the row #s, such as this
(draft coding):
===========================
if activecell.row >= 103 and
if activecell.row <= 135 then
print From:=3. To:=3 'i don't know the print
'structure off the top
'of my head
if activecell.row >= 203 and
if activecell.row <= 235 then
print From:=5. To:=5
===========================
that type of thing........... or could make it a case statement
structure.
caveat - it will only work if no additional rows will be added in the
future, but it can be done.........
hope this helps.
susan

 

[JW]

Hi Don. Have a look at Allen Wyatt's solution. This code simply
places the page number in a message box, but you could easily change
this to return the page number to use in a print statement.
http://exceltips.vitalnews.com/Pages/T003135_Page_Numbers_in_VBA.html

 

[JW]

Don, here is a quickly tweaked version of Allen's code. Just turned
it into a function and added a few things here and there and got rid
of the old Excel4 macro. To use it, just call the printpage the
procedure below it. Keep in mind, there is no error handling in this
procedure. Feel free to add that in if you see fit.

Function PageInfo(currCell As Range)
Dim iPages As Integer
Dim iCol As Integer
Dim iCols As Integer
Dim lRows As Long
Dim lRow As Long
Dim x As Long
Dim y As Long
Dim hBreaks As Long
Dim vBreaks As Long
Application.ScreenUpdating = False
ActiveWindow.View = xlPageBreakPreview
hBreaks = Worksheets(1).HPageBreaks.Count
vBreaks = Worksheets(1).VPageBreaks.Count
iPages = (hBreaks + 1) * (vBreaks + 1)
With ActiveSheet
y = currCell.Column
iCols = .VPageBreaks.Count
x = 0
Do
x = x + 1
Loop Until x = iCols _
Or y < .VPageBreaks(x).Location.Column
iCol = x
If y >= .VPageBreaks(x).Location.Column Then
iCol = iCol + 1
End If
y = ActiveCell.Row
lRows = .HPageBreaks.Count
x = 0
Do
x = x + 1
Loop Until x = lRows _
Or y < .HPageBreaks(x).Location.Row
lRow = x
If y >= .HPageBreaks(x).Location.Row Then
lRow = lRow + 1
End If
If .PageSetup.Order = xlDownThenOver Then
PageInfo = (iCol - 1) * (lRows + 1) + lRow
Else
PageInfo = (lRow - 1) * (iCols + 1) + iCol
End If
End With
Application.ScreenUpdating = True
ActiveWindow.View = xlNormalView
End Function

Sub printpage()
Dim p As Long
p = PageInfo(ActiveCell)
ActiveSheet.PrintOut From:=p, To:=p
End Sub

 

[Don Lowe]

JW,

I have tried the code you gave me. I continue to get "Run-Time Error 9".
Then I can not get it to work when I go to debug the highlighted area:

Loop Until x = iCols _
Or y < .VPageBreaks(x).Location.Column

What am I doing wrong?

 

[JW]

JW,

I have tried the code you gave me. I continue to get "Run-Time Error 9".
Then I can not get it to work when I go to debug the highlighted area:

Loop Until x = iCols _
            Or y < .VPageBreaks(x).Location.Column

What am I doing wrong?

Not sure. Works just fine for me here. What version of Excel are you
running (I'm running 97 and 2003)? You can send me your workbook
containing the code and I will take a look at it.

 

[Don Lowe]

JW,

I am using Excel 2003. What is your e-mail? I will send you the workbook.