Bit of excel VB help please

peige · 12 May 2011 at 13:31

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

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

Can I have a little help please

csmager · 13 May 2011 at 07:16

It's putting 1 in there because the code says to put 1 in there and increment that.

Do you want to put the existing value in there and increment from there?

Souleh · 13 May 2011 at 10:47

Edit*

Just tried it and it seems to be incrementing ok ?

peige · 13 May 2011 at 14:29

I need it to take the value thats in there to start with and then increment from there, apart from that, yes its working

csmager · 13 May 2011 at 14:42

In which case, you need to read the value off the sheet first. Something along these lines:

startValue = ActiveSheet.Range("G8").Value
endValue = startValue + CopiesCount

For CopyNumber = startValue to endValue

peige · 13 May 2011 at 18:41

Sorted, thanks very much

Can you believe the lady that did this sheet was printing 50 copies and then using an incrementing number stamp to put the order number on each one. Now they will come out the printer ready to go :cool: