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!

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
--
Gary''s Student - gsnu200780


"no-one-but-me" wrote:

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!

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

Bit confusing for thicko me/! :)

"Gary''s Student" wrote:

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
--
Gary''s Student - gsnu200780


"no-one-but-me" wrote:

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!

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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"no-one-but-me" wrote in message
...
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!

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




--
Gary''s Student - gsnu200781


"no-one-but-me" wrote:

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

Bit confusing for thicko me/! :)

"Gary''s Student" wrote:

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
--
Gary''s Student - gsnu200780


"no-one-but-me" wrote:

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!

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?

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.

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



no-one-but-me wrote:

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?

--

Dave Peterson

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

no-one-but-me wrote:

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

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




--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Dave Peterson" wrote in message ...
Record a macro when you change the printer manually and you'll have the code.

no-one-but-me wrote:

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