Sequential Page Numbering

Topic

New Reply

The model I’m working on has multi-page and single page worksheet printouts which are printed sequentially as separate print jobs, via macro. Is there a way to determine the total pages from the previous print job so that the next print job page number can be set sequentially.

Reply | Quote

Replies

Why can’t you print them all at the same time? ie hold shift and select each sheet to print and then print

(sorry if I missed the point)

Reply | Quote

Tks for your reply. The simplest solution is always the preferred one. However, I have the model set up so that within a worksheet, the output pages do not necessarily appear sequentially; e.g., a page of calculations may have an explanations page to the right, followed by several calculation pages beneath the first one.

What I had in mind was to somehow reference the &N value (Total Pages) available with Excel footer through VBA. Is this possible?

Reply | Quote

If you select the sheets to be printed (ctrl-click for non-adjacent sheets) the total number of pages will calculate correctly so the “page X of Y” will come out properly if you have that set in the footer.

If you do the equivalent action in VBA by adding the required sheets to the selection and then printing the selection, I imagine it would work properly, although I have not tried it. I’m off to waste a couple of sheets of paper – I’ll let you know!

Reply | Quote

Yup – it seems to work.

Try something like:

    Sheets(Array("Summary", "Registration", "Memberships", "Equipment")).Select
    Sheets("Summary").Activate
    ActiveWindow.SelectedSheets.PrintOut

obviously, you’ll have to change the sheetnames to match your workbook!

and you should get what you need.

Reply | Quote

Or you could also refer to sheets by number:

Sheets(Array(1,3,4,7)).Select
sheets(1).Activate

It might depend on whether you’re more likely to rename a sheet, or to move/insert a sheet.

Referring by sheet number makes the code easier to use in another workbook.

Reply | Quote

I suppose I find that I am more likely to move a sheet than to rename it! Even if not, I think that using sheet names is preferable, since it is obvious what you are referring to; a user six months later looking at the code (say to add a sheet to the standard output) will see the names and understand the purpose, where the reference by index number may not tip them off….

If the code is to be used in another workbook, I would almost prefer that the sheetnames be used, since then I KNOW that the code won’t work, and will generate an error (subscript out of range, I expect), rather than ‘sort of’ working by printing sheets other than the ones I want (I can’t imagine that I would want the first, third, fourth and seventh sheets printed in two otherwise-unrelated workbooks!). Since I will have to modify the code anyway, I might as well set myself the task of looking for something meaningful to me, rather than to the computer.

Reply | Quote

As far as I know, the only way to really determine the page count programmatically is to use the Excel 4 (XLM) Get.Document macro function. You can execute this macro function from VBA. Here you have some code that loops through all the worksheets in the active workbook and counts the number of pages.

Sub TotalNumberOfPages()
Dim Total As Integer
Dim Sht As Worksheet
Dim pg As Integer
Total = 0
For Each Sht In Worksheets
Sht.Activate
pg = ExecuteExcel4Macro(“Get.Document(50)”)
Total = Total + pg
Next Sht
MsgBox “Total Number of Pages to be printed = ” & Str$(Total)
End Sub

Reply | Quote

Thanks for the example. I used the Excel4 macro GetDocument to update the first page number for the next printout. Tks to all who replied.

Reply | Quote