Bit of excel VB help please !

General Computing Coding and Design
1

Got a spreadsheet thats a simple order form we need to print over and over, there are two order forms on the sheet (we cut the sheet of A4 in half to make two A5 orders)
Each sheet needs to print with a sequential number on it starting with the number in existing cell on the left form and flowing on through the print run 1 by 1.
I’ve got the beginning of the code

Sub INCPRINT()
Dim CopiesCount As Long
Dim CopieNumber As Long
CopiesCount = Application.InputBox("How many Copies do you want?", Type:=1)

For CopieNumber = 1 To CopiesCount
With ActiveSheet
'number in cell G8 (prints "n")
.Range("G8").Value = CopieNumber

'number in cell G8 (prints "n of z")
'.Range("G8").Value = CopieNumber & " of " & CopiesCount

'number in the footer (prints "n of z")
'.PageSetup.LeftFooter = CopieNumber & " of " & CopiesCount

'Print the sheet
.PrintOut
End With
Next CopieNumber
End Sub

but its not complete and its not working, its taking the number in G8 and instead of taking it and adding 1 and printing as I want its putting 1 in there and then working… that and I’ve not worked out how to get it to put then next number in O8 ether :smiley:
Can I have a little help please :slight_smile:

2

I’ll make sure Scream comes into this thread as he’ll be sober in the morning, I doubt I will :wasted:

3

I’ll take the help, sober or otherwise :wink:

Searching around you’d think this problem wouldn’t be a new one but the internet seems full of half solutions :s

4

Heh!

5

:Poke:

6

pm inbound…

Cheers

Sent using Tapatalk

7

Long time no see Shanks. :wave: How things with you m8?

8

Blimey shanks, twice in one year we’ve crossed paths now! :wink:

I just cracked it and have a working solution now gents :cool:

9

Hi Droid, yeah all going well here thanks. Usual story of real life getting in the way of internet usage. Broke cover for a short time from my usual lurking at 3am :slight_smile:

Pleased it’s sorted Peige, Cheers

10

Glad that all’s well with you m8. :thumbsup:

11

[QUOTE=Peige;460217]
I just cracked it and have a working solution now gents :cool:[/QUOTE]

I Know how I would have done the CopieNumber=CopieNumber+1 do while CopiesCount<=CopieNumber loop …what solution did you come up with

12

Below is whats working, as I needed the count to go up two as the sheet itself is taking the number in cell f8 and adding one to it for the split page print.

Only extra question I am scrabbling around to answer is can I make this run from a “button” on the main sheet as I can’t see the people concerned finding the macro tab/button/run bit so it will end up being a “me” job still, all be it an easy one!
I’ve read this can be done by editing the toolbar but this will be run on different pc’s so thats not really practical…

Sub INCPRINT()

Dim CopiesCount As Long
Dim CopieNumber As Long
CopiesCount = Application.InputBox("How many Order numbers to print?", Type:=1)

StartValue = ActiveSheet.Range("f8").Value
endValue = StartValue + CopiesCount

For CopieNumber = StartValue To endValue

With ActiveSheet
'number in cell f8 (prints "n")
.Range("f8").Value = CopieNumber
CopieNumber = CopieNumber + 1

'number in cell f8 (prints "n of z")
'.Range("G8").Value = CopieNumber & " of " & CopiesCount

'Print the sheet
.PrintOut
End With

Next CopieNumber
End Sub
13

Ok, so that wasn’t too hard. Now has a button in the middle of the sheet which kicks it off :smiley: