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" <Nefermet@discussions.microsoft.com> wrote in message
news:9E03EB41-1617-441B-A1DF-575E151B104B@microsoft.com...
> 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" <Nefermet@discussions.microsoft.com> wrote in message
news:54CFB26C-A699-4160-AAA2-20E13956672B@microsoft.com...
> 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" <Nefermet@discussions.microsoft.com> wrote in message
> news:54CFB26C-A699-4160-AAA2-20E13956672B@microsoft.com...
> > 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.