Printing Sequential Numbers in Excel after Print

[starflt1701]

I thought I posted this question yesterday, but cannot find it in the list. I apologize if this is a duplicated thread.

Anyway, I would like to print multiple copies of a single page worksheet, but incorporate a formula whereby with each copy printed, a unique, sequential number is displayed on the form (example: Copy 1, Copy 2, etc.).

Is there any way to do this?

Thanks!

 

[SiliconXu]

open the VBE and then type the code below in the "ThisWorkbook" object:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Static ab As Long
ab = ab + 1
End Sub

Here I use "Static" to declare the variable "ab" to make sure it retains its value as long as the code is running. Each time you click "Print", the variable will add 1.

 

[xlhelp]

Sub Copies()
nc = Application.InputBox(Prompt:="Number of copies?", Type:=1)
For pg = 1 To nc
ActiveSheet.PageSetup.RightHeader = pg
ActiveWindow.SelectedSheets.PrintOut Copies:=1
Next
End Sub


Vita Brevis

 

[starflt1701]

Thanks for the replies. I can't wait to try these out!

 

[starflt1701]

apparently I need a little more help. I copied both the above subroutines into VBA for the worksheet. Nothing is printing on the copies other than the data on the spreadsheet. I thought perhaps I needed to create a custom header showing the page number, but when I printed, each page showed page 1.

I think I'm missing an important step in this process and don't know what that is.

Thanks!

 

[SkipVought]

Hi,

1. Name the range where you want the NUMBER, copy

2. right click on the sheet tab and select View Code

3. ctr+R to view the Project Explorer.

4. Doubleclick the ThisWorkbook Object in the Project Explorer.

5. In the ThisWorkbook Code Window, paste...

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    [copy] = [copy] + 1
End Sub

6. PRINT

Whatever number is in the cell named copy will incirment by 1.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[starflt1701]

Thanks. I got it now, and it works great! Greatly appreciated.