Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Resetting page numbers
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Resetting page numbers
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 "Vince" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Resetting page numbers
-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 "Bill Pfister" wrote: 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 "Vince" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Resetting page numbers
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 "Vince" wrote: -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 "Bill Pfister" wrote: 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 "Vince" wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Resetting page numbers
Will appreciate some help with the code. Yes, I am printing "Page x of pages
Y", which I'll like to continue. Thanks Vince "Bill Pfister" wrote: 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 "Vince" wrote: -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 "Bill Pfister" wrote: 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 "Vince" wrote: 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Resetting page numbers
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? "Vince" wrote: Will appreciate some help with the code. Yes, I am printing "Page x of pages Y", which I'll like to continue. Thanks Vince "Bill Pfister" wrote: 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 "Vince" wrote: -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 "Bill Pfister" wrote: 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 "Vince" wrote: 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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Resetting page numbers
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).A ddress) 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.Are as.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 "Bill Pfister" wrote: 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? "Vince" wrote: Will appreciate some help with the code. Yes, I am printing "Page x of pages Y", which I'll like to continue. Thanks Vince "Bill Pfister" wrote: 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 "Vince" wrote: -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 "Bill Pfister" wrote: 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 "Vince" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Big background page numbers/ Can't change format of a range | New Users to Excel | |||
Why are 1/2 my numbers imported as text and the rest as numbers? | Excel Discussion (Misc queries) | |||
Page Numbers W/i Spreadsheet | Excel Discussion (Misc queries) | |||
IN EXCEL, TURN OFF THE BIG GRAY NUMBERS IN THE PAGE BREAK PREVIEW | Excel Discussion (Misc queries) | |||
adding a new page break to an existing page break | Excel Discussion (Misc queries) |