Printing document references

[lobster]

Hello,

We have a number of forms which we have produced here at work which
require numbered references on them so each document is unique. Can
this be batch driven ?

EG.

I have designed a printed sheet in Excel for our stock take this year.

We need 200 sheets to be issued out to the guys doing the counting.

Each sheet needs its own unique documents reference number on it so it
can be traced.

How can I automate this so I can print 200 copies of the document, and
the document references increase incrementally on each separate page.
EG. ABC0001, ABC0002, ABC0003...

I dont want to have to handwrite references onto each document and I
dont want to print one copy at a time and manually adjust the
reference.

Any help appreciated, in fact its going so badly today at work that
even abuse would go down reasonably well. Many thanks.

 

[Nikos Yannacopoulos]

Try using the following code:

Sub Print_Numbered_Copies()
Dim NoOfCopies As Integer

Get_input:
On Error GoTo invalid_input
NoOfCopies = InputBox("How many copies do you
want?", "Please enter number of copies")
On Error GoTo 0
If NoOfCopies <= 0 Then GoTo invalid_input

CurrCount = Val(Right(Range("A1").Value, 4))
Prefix = Left(Range("A1").Value, 3)

For i = 1 To NoOfCopies
ActiveWindow.SelectedSheets.PrintOut Copies:=1, _
Collate:=True
Range("A1").Value = Prefix & _
Format(CurrCount + 1, "#0000")
CurrCount = CurrCount + 1
Next

Exit Sub

invalid_input:
MsgBox "Your entry must be a positive integer. Pls try
again!", vbExclamation
GoTo Get_input

End Sub

Assumption: form number in cell A1 (or modify code
accordingly)
The code will always start printing from the next number
from the current in cell A1 (so you can start from any
number just by changing A1; to start from 1, enter ABC0000
in A1).
Caution: depending on page complexity and your printer,
there is a limit to how many pages you can print in a go
before you hit a buffer overrun!

Nikos

-----Original Message-----

Hello,

We have a number of forms which we have produced here at work which
require numbered references on them so each document is unique. Can
this be batch driven ?

EG.

I have designed a printed sheet in Excel for our stock take this year.

We need 200 sheets to be issued out to the guys doing the counting.

Each sheet needs its own unique documents reference number on it so it
can be traced.

How can I automate this so I can print 200 copies of the document, and
the document references increase incrementally on each separate page.
EG. ABC0001, ABC0002, ABC0003...

I dont want to have to handwrite references onto each document and I
dont want to print one copy at a time and manually adjust the
reference.

Any help appreciated, in fact its going so badly today at work that
even abuse would go down reasonably well. Many thanks.


------------------------------------------------

~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step

guide to creating financial statements

 

[Nikos Yannacopoulos]

Sorry, the For loop in my original reply has to be changed
as follows:

For i = 1 To NoOfCopies
CurrCount = CurrCount + 1
ActiveWindow.SelectedSheets.PrintOut Copies:=1,
Collate:=True
Range("A1").Value = Prefix & Format(CurrCount +
1, "#0000")
Next

so as to start printing from the next number... as it was
it starts from the current.

Nikos

-----Original Message-----

Hello,

We have a number of forms which we have produced here at work which
require numbered references on them so each document is unique. Can
this be batch driven ?

EG.

I have designed a printed sheet in Excel for our stock take this year.

We need 200 sheets to be issued out to the guys doing the counting.

Each sheet needs its own unique documents reference number on it so it
can be traced.

How can I automate this so I can print 200 copies of the document, and
the document references increase incrementally on each separate page.
EG. ABC0001, ABC0002, ABC0003...

I dont want to have to handwrite references onto each document and I
dont want to print one copy at a time and manually adjust the
reference.

Any help appreciated, in fact its going so badly today at work that
even abuse would go down reasonably well. Many thanks.


------------------------------------------------

~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step

guide to creating financial statements

 

[lobster]

Nikos

Thanks for all your help, really appreciated. Have just run the script
and it all works fine. Extremely grateful for your prompt response.

 

[lobster]

Just one more thing.

This works fine as i said before. The only thing is that if i set cell
A1 to value "ABC0000" then send 5 prints the next number generated is
"ABC0002" rather than "ABC0001".

If there is a nice easy way around this then that would be great, if
not dont worry about it as it isnt too much of a problem.

Again many thanks.