How do I get a different number on each copy of the same page?

[no-one-but-me]

I need help with a project at work...

I have created a spreadsheet as part of a project at work.

It's now that I have completed the sheet, I have stumbled across an issue...

Could anyone tell me how to get a different number in the header(for the
worksheet reference) on each print out?

For Example; If I printed out 50 copies of this spreadsheet, the first copy
would have the reference 001, the second copy would have 002...third copy
003... etc

I'm aware that if I just did it as a normal header it I would have loads of
copies of ref: 001

Please help....! It's driving me insane as I'm sure there is a way to do
this but can't figure it out!

 

[Gary''s Student]

Rather than use the menu to print multiple copies, try an adaptation of this
simple macro:

Sub NoOneButMe()
n = Application.InputBox(Prompt:="Enter number of copies:", Type:=1)
For i = 1 To n
ActiveSheet.PageSetup.CenterHeader = i
ActiveWindow.SelectedSheets.PrintOut Copies:=1
Next
End Sub

 

[no-one-but-me]

hahaha.. Could you elaborate a bit more on that haha!

Bit confusing for thicko me/!

 

[Ron de Bruin]

See also
http://www.rondebruin.nl/print.htm#same

Copy the macro in a normal module
See this page
http://www.rondebruin.nl/code.htm

 

[Gary''s Student]

Macros are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To use the macro from the normal Excel window:

1. ALT-F8
2. Select the macro
3. Touch Run



To remove the macro:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[no-one-but-me]

Wow! That's fantastic! Thanks so much! Now can you tell me just one more
thing.. what would I change in the code to get the reference starting at
000001 rather than just 1 single?

 

[no-one-but-me]

Oh and also... How would I change the default printer? We have several
printers here and it automatically goes to the one that I won't be printing
it out on when I'm finished the project.

 

[Dave Peterson]

ActiveSheet.PageSetup.CenterHeader = i
becomes
ActiveSheet.PageSetup.CenterHeader = format(i, "000000")

 

[Dave Peterson]

Record a macro when you change the printer manually and you'll have the code.

 

[Ron de Bruin]

I want to add this to Dave's reply

If it is possible that the Ne number can change use this function.
See this test macro

Sub Test()
Dim str As String
Dim strNetworkPrinter As String
str = Application.ActivePrinter

strNetworkPrinter = GetFullNetworkPrinterName("Adobe PDF")
If Len(strNetworkPrinter) > 0 Then
Application.ActivePrinter = strNetworkPrinter
ActiveSheet.PrintOut
End If

Application.ActivePrinter = str
End Sub


Function GetFullNetworkPrinterName(strNetworkPrinterName As String) As String
Dim strCurrentPrinterName As String, strTempPrinterName As String, i As Long
strCurrentPrinterName = Application.ActivePrinter
i = 0
Do While i < 100
strTempPrinterName = strNetworkPrinterName & " on Ne" & Format(i, "00") & ":"
On Error Resume Next ' try to change to the network printer
Application.ActivePrinter = strTempPrinterName
On Error GoTo 0
If Application.ActivePrinter = strTempPrinterName Then
GetFullNetworkPrinterName = strTempPrinterName
i = 100 ' makes the loop end
End If
i = i + 1
Loop
Application.ActivePrinter = strCurrentPrinterName ' change back to the original printer
End Function