Automatically incrementing number for each print
 

I'm trying to create a jobsheet that will automatically increment the
jobsheet number each time it is printed. Is this possible?

Only through VBA code. If you're willing to go that route, post back with
details and one of us will offer more detailed advice

"Nefermet" wrote:

I'm trying to create a jobsheet that will automatically increment the
jobsheet number each time it is printed. Is this possible?

You could use the before print event to increment a value:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Worksheets("Sheet1").Range("A1").Value = Worksheets("Sheet1").Range("A1").Value + 1
End Sub

Note that this will only work if you print only one sheet at a time - printing multiple copies only
fires this event once.

Otherwise, you could use a macro to print multiple copies:

Sub PrintEm()
Dim i As Integer
For i = 1 to 100
Worksheets("Sheet1").Range("A1").Value = Worksheets("Sheet1").Range("A1").Value + 1
Worksheets("Sheet1").PrintOut
Next i
End Sub

HTH,
Bernie
MS Excel MVP


"Nefermet" wrote in message
...
I'm trying to create a jobsheet that will automatically increment the
jobsheet number each time it is printed. Is this possible?

Hi Duke and Bernie,

I've had a look at the solution provided by Bernie, the second macro looks
most promising except that it will fire out the set qty I want straight away
without giving me the option to put two on one sheet (they're only A5 size).
Or can I set this in the printing options?

Thanks for your help, its exactly what I'm after so far!

Nefermet

"Duke Carey" wrote:

Only through VBA code. If you're willing to go that route, post back with
details and one of us will offer more detailed advice

"Nefermet" wrote:

I'm trying to create a jobsheet that will automatically increment the
jobsheet number each time it is printed. Is this possible?

Duke,

Try copying your print range and pasting below your current print range, then change your print are
to include both. Then you could use two lines of code instead of one, along the line of:

Sub PrintEm()
Dim i As Integer
For i = 1 to 50

Worksheets("Sheet1").Range("A1").Value = Worksheets("Sheet1").Range("A1").Value + 2
Worksheets("Sheet1").Range("A15").Value = Worksheets("Sheet1").Range("A15").Value + 2
Worksheets("Sheet1").PrintOut
Next i
End Sub

HTH,
Bernie
MS Excel MVP


"Nefermet" wrote in message
...
Hi Duke and Bernie,

I've had a look at the solution provided by Bernie, the second macro looks
most promising except that it will fire out the set qty I want straight away
without giving me the option to put two on one sheet (they're only A5 size).
Or can I set this in the printing options?

Thanks for your help, its exactly what I'm after so far!

Nefermet

"Duke Carey" wrote:

Only through VBA code. If you're willing to go that route, post back with
details and one of us will offer more detailed advice

"Nefermet" wrote:

I'm trying to create a jobsheet that will automatically increment the
jobsheet number each time it is printed. Is this possible?

Brilliant! with a little bit of tweeking that works perfectly!

Thanks for your help.

Nefermet

"Bernie Deitrick" wrote:

Duke,

Try copying your print range and pasting below your current print range, then change your print are
to include both. Then you could use two lines of code instead of one, along the line of:

Sub PrintEm()
Dim i As Integer
For i = 1 to 50

Worksheets("Sheet1").Range("A1").Value = Worksheets("Sheet1").Range("A1").Value + 2
Worksheets("Sheet1").Range("A15").Value = Worksheets("Sheet1").Range("A15").Value + 2
Worksheets("Sheet1").PrintOut
Next i
End Sub

HTH,
Bernie
MS Excel MVP


"Nefermet" wrote in message
...
Hi Duke and Bernie,

I've had a look at the solution provided by Bernie, the second macro looks
most promising except that it will fire out the set qty I want straight away
without giving me the option to put two on one sheet (they're only A5 size).
Or can I set this in the printing options?

Thanks for your help, its exactly what I'm after so far!

Nefermet

"Duke Carey" wrote:

Only through VBA code. If you're willing to go that route, post back with
details and one of us will offer more detailed advice

"Nefermet" wrote:

I'm trying to create a jobsheet that will automatically increment the
jobsheet number each time it is printed. Is this possible?

Nefermet,

Glad to hear it worked! And sorry about addressing you as "Duke" last reply....

Bernie
MS Excel MVP

Brilliant! with a little bit of tweeking that works perfectly!

Thanks for your help.

Automatically incrementing number for each print
 

Sorry for jumping in but I think I am trying to d something similar.

I have 4 tables within one sheet in one excel file which i need to print 50
times to get a total of 200 copies. I would like each table to have a
unique reference number 1,2,3,4. The cells I need to fill are S6, AS6, S59
and AS59, which will have numbers 1-4 on the first sheet printed, (one number
per
table that is), 5-9 on the second etc.

Is it possible to adapt the solution u proposed here to do that? I am
completely clueless on VBA.

Thanks

"Bernie Deitrick" wrote:

You could use the before print event to increment a value:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Worksheets("Sheet1").Range("A1").Value = Worksheets("Sheet1").Range("A1").Value + 1
End Sub

Note that this will only work if you print only one sheet at a time - printing multiple copies only
fires this event once.

Otherwise, you could use a macro to print multiple copies:

Sub PrintEm()
Dim i As Integer
For i = 1 to 100
Worksheets("Sheet1").Range("A1").Value = Worksheets("Sheet1").Range("A1").Value + 1
Worksheets("Sheet1").PrintOut
Next i
End Sub

HTH,
Bernie
MS Excel MVP


"Nefermet" wrote in message
...
I'm trying to create a jobsheet that will automatically increment the
jobsheet number each time it is printed. Is this possible?