Page re-referencing

[sparx]

Is there a way to print pages using a starting page number of choice?.
I want to select a group of worksheets, could be anywhere in the workbook and upon print, enter the desired starting number then to count up from that number?

I am using excel 2002

Any help appreciated.

[shg]

You mean like setting the print range (Pages from: ___ to: ___) when you do File > Print?

[sparx]

Hi, its a bit hard to explain but here goes.

When you setup pages and add [page] of [pages], pressing print or print preview will display in the footer page 1 of ??, page 2 of ?? etc etc.
I don't want it to say page 1 of ?? then page 2 of ?? etc, I want to be able to print the selected sheets with a starting page number of my choice.

Even though I may select 20 worksheets ( normally print 1 of 20 etc ), I want it to select the starting number then have the sheets count up from the ?? to the last page.

I have 5 workbooks, each with 20 worksheets ( each sheet being 1 x A4 page ) and need to produce a document 100 pages long but need 20 pages from each of the 5 files.

I set the last page to 100 then print sheets 1 to 20 from each file, the 1st file I enter 1 to 100 ( 20 pages ), 2nd file I enter 21 to 100 ( 20 pages ), 3rd file 41 to 100 etc till
I have 5 pdf files each having 20 sheets in them, at that point I merge all 5 pdf's to produce 1 massive 100 page document.

This way I can produce 1 document from 5 workbooks 100 pages long and its seamless.

Hope this makes sense.

[sparx]

Hi, here is the code I am trying to use, it asks for the 1st and last page numbers and always counts from page 1, I just want to call page 1 a different number may be 21 or 41.

Private Sub Workbook_BeforePrint(Cancel As Boolean)

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Dim Num1 As Long
Dim Num2 As Long

Num1 = Application.InputBox(Prompt:="Please enter the First page.", Title:="Starting Page No", Type:=1)
Num2 = Application.InputBox(Prompt:="Please enter the Last page.", Title:="Ending Page No", Type:=1)

Dim wrkSht As Worksheet
For Each wrkSht In ActiveWindow.SelectedSheets

wrkSht.PageSetup.LeftFooter = "Page " & "&P of " & Num2

Next wrkSht

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

[sparx]

SOLVED IT.

Private Sub Workbook_BeforePrint(Cancel As Boolean)

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Dim Num1 As Long
Dim Num2 As Long

Num1 = 0

Num1 = Application.InputBox(Prompt:="Please enter the First page.", Title:="Starting Page No", Type:=1)
Num2 = Application.InputBox(Prompt:="Please enter the Last page.", Title:="Ending Page No", Type:=1)

Dim wrkSht As Worksheet
For Each wrkSht In ActiveWindow.SelectedSheets
wrkSht.PageSetup.LeftFooter = "Page " & Num1 & " of " & Num2
Num1 = Num1 + 1
Next wrkSht

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub