Help with total page number and number of copies

  • Thread starter Thread starter Guy Incognito
  • Start date Start date
G

Guy Incognito

I'm trying to have displayed, somewhere in the footer, the total numbe
of pages printed (rather than just the total number of pages); so if
have one sheet and print it twelve times it say the page total i
twelve on the sheet
 
Hi
which Excel version do you have?. There should be a symbol in the
custom header/footer section to insert this information
 
Hi
- goto the menu 'File- Page Setup - Header/Footer'
- click the custom header/footer button
- to insert the number of pages choose the button with the two '+'
signs (third from the left)
 
I believe Frank, that you need code to accomplish what the OP is looking to
do.

That command will not increment with multiple copies of the same page.
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

Hi
- goto the menu 'File- Page Setup - Header/Footer'
- click the custom header/footer button
- to insert the number of pages choose the button with the two '+'
signs (third from the left)
 
I think you'll want to find a spot to keep track of the number of times the
sheet's been printed--maybe a cell on a hidden worksheet???

But there are couple of problems with this.

One if you close the workbook without saving, then the number in that cell won't
be saved. If that's a problem, you could save that counter in an external file
(or even the windows registry).

And another problem is when to increment that cell. You could increment it in
the workbook_beforeprint event. But that fires when you do a print preview,
too. And you wouldn't know how many copie the user chose to print--or if they
even chose to print that sheet.

I think I'd provide a macro (maybe called from a Forms Button placed on that
worksheet) that did all the work.

If you don't want that button to show up, place it outside the print range or
rightclick on it, choose "format control", and on the properties tab, make sure
that "print object" is not checked.

This is a version of a macro that might get you started:

Option Explicit
Sub CountAndPrint()
Dim myCtrCell As Range
Dim myVal As Long

Set myCtrCell = Worksheets("sheet2").Range("A1")

myVal = CLng(Application.InputBox(Prompt:="How many copies?", Type:=1))

If myVal < 1 Then
Exit Sub
End If

'no validity to check that this cell is numeric!
myCtrCell.Value = myCtrCell.Value + myVal

ActiveSheet.PrintOut copies:=myVal ', preview:=True

ActiveSheet.Parent.Save

End Sub

(I included that .save command.)
 
Hi
you're right. Misread/overread the 'multiple copies' part. Yes this
would require some code.

For the OP: try the following macro to insert the total pages
(inclduing copies) in your header:

Sub insert_pages()
Dim wks As Worksheet
Dim numPages
Dim copies
Dim total_pages
Set wks = ActiveSheet
copies = 4 'change this
numPages = Application.ExecuteExcel4Macro("Get.Document(50)")
total_pages = copies * numPages
With wks.PageSetup
.CenterHeader = "pages: " & total_pages

End With
End Sub
 
Back
Top