Resetting page numbers

V

Vince

I have a worksheet which prints 20 pages at the current print setting. I have
one manual page break.

Is it possible to have excel restart the page numbers from that page break.

your help is appreciated.

Vince
 
B

Bill Pfister

Vince, a couple questions to help find a solution:
-Is there one manual pagebreak on each page or just on one of the pages?
-Are there any automatic pagebreaks?
-Would you be willing to use macros for printing?

Regards,
Bill
 
V

Vince

-There is one manual pagebreak on one of the pages
-There are automatic page breaks
-I am willing to use macros (if there are no other solutions)

Thanks

Vince
 
B

Bill Pfister

Using a macro, you can print the job as two sections. The first print job
will be up to the manual break, then the second print job will be everything
after the break. Within the macros, you can specify the starting page
number. The only issue will be if you're printing "page x of pages y",
because "y" will not be accurate without some manipulation. Make sense?

Let me know if you need help with the code.

Bill
 
V

Vince

Will appreciate some help with the code. Yes, I am printing "Page x of pages
Y", which I'll like to continue.

Thanks

Vince
 
B

Bill Pfister

Two more questions:
1) Do you set the PrintArea property?
2) Is your print range oriented vertically on one sheet (page 2 is below
page 1, etc.) - in other words, all the pagebreaks (whether manual or
automatic) are all horizontal breaks?
 
B

Bill Pfister

Here's an example that makes a few assumptions. First one is that you set
the PrintArea for the sheet you want to print. Second is that you want to
print the ActiveSheet. I left some additional code in there - it's from
another project I did - but it's harmless and you might even want it for
future expanded capabilities.





Public Sub SpecialPrint()
Dim wkb As Workbook
Dim wks As Worksheet
Dim rng As Range
Dim rng1 As Range
Dim rng2 As Range
Dim lngPageView As Long
Dim lngCountBreaks As Long
Dim lngCountAreas As Long
Dim lngPBRow As Long
Dim lngCountPages1 As Long
Dim lngCountPages2 As Long
Dim blnBeforePB As Boolean
Dim rngTest As Range
Dim i As Long
Dim j As Long
Dim lngOffset As Long

Set wkb = ThisWorkbook
'Set wks = wkb.Worksheets("PrintMe")
Set wks = wkb.Activesheet
'Set rng = wks.Range("A1:" &
wks.Range("A1").SpecialCells(xlCellTypeLastCell).Address)
Set rng = wks.Range(wks.PageSetup.PrintArea)

lngPageView = rng.Parent.Parent.Parent.ActiveWindow.View
rng.Parent.Parent.Parent.ActiveWindow.View = xlPageBreakPreview

lngCountPages1 = 1
lngCountPages2 = 1
lngCountBreaks = rng.Parent.HPageBreaks.Count
lngCountAreas = rng.Areas.Count
lngPBRow = -1

' Loop through all the areas of the original selection and determine if
any pagebreaks are contained within
For i = 1 To lngCountAreas
For j = 1 To lngCountBreaks

'If (rngSrc.Parent.HPageBreaks(j).Type = xlPageBreakManual) Then
If (PageBreakType(rng.Parent.HPageBreaks, j) =
xlPageBreakManual) Then
Set rngTest = rng.Application.Intersect(rng.Areas(i),
rng.Parent.HPageBreaks(j).Location.EntireRow)

' Retain the pagebreaks (relative position) if they were
manually set
If Not (rngTest Is Nothing) Then
lngPBRow = rng.Parent.HPageBreaks(j).Location.Row -
rng.Areas(i).Row
lngCountPages1 = lngCountPages1 + 1
End If
Else
Set rngTest = rng.Application.Intersect(rng.Areas(i),
rng.Parent.HPageBreaks(j).Location.EntireRow)

If Not (rngTest Is Nothing) Then
lngCountPages1 = lngCountPages1 + 1
End If
End If

Next j
Next i

If (lngPBRow > -1) Then
Set rng1 = rng.Parent.Range(rng.Cells(1).Address & ":" &
wks.Cells(lngPBRow, rng.Cells(rng.Cells.Count).Column).Address)
Set rng2 = rng.Parent.Range(wks.Cells(lngPBRow + 1,
rng.Cells(rng.Cells.Count).Column).Address & ":" &
rng.Cells(rng.Cells.Count).Address)
'Set rng2 = rng.Parent.Range(lngPBRow + 1 & ":" &
rng.Areas(rng.Areas.Count).Cells(rng.Areas(rng.Areas.Count).Cells.Count).Row)

Debug.Print "1: " & rng1.Address
Debug.Print "2: " & rng2.Address

wks.PageSetup.PrintArea = rng1.Address
wks.PageSetup.FirstPageNumber = 1
wks.PageSetup.CenterFooter = "Page &P of &N"
wks.PrintOut

wks.PageSetup.PrintArea = rng2.Address
wks.PageSetup.FirstPageNumber = 1
wks.PageSetup.CenterFooter = "Page &P of &N"
wks.PrintOut

End If

wks.PageSetup.PrintArea = rng.Address
rng.Parent.Parent.Parent.ActiveWindow.View = lngPageView

End Sub



Private Function PageBreakType(objPageBreaks As Object, lngIndex As Long) As
Long
Dim lngReturn As Long

On Error GoTo ErrHandler

lngReturn = 0

lngReturn = objPageBreaks(lngIndex).Type
PageBreakType = lngReturn

Exit Function
ErrHandler:
lngReturn = 1
PageBreakType = lngReturn
End Function
 
Top