Increse number on Print 1

[gizmo1973]

Hi all,

Quick on for you; I'm sure I have seen this before but I cannot find it.

In Excel 2003 I have a cell which says MMS/000001 and what I am after is for this cell to increase by one every time the sheet is printed.

ie MMS/000001 MMS/000002 MMS/000003

So on and so forth, I was going to use the mail merge but I am in Excel!!

As I say I'm sure someon has asked this befroe but I cannot find it on search, help????

Regards, Phil.

http://www.manchester-medical-services.co.uk

 

[xlbo]

Hey Gizmo - you'll need to use the BEFOREPRINT event in VBA to achieve this

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[gizmo1973]

Cheers Geoff,

Searching for BEFOREPRINT brought up many solutions but for some reason I could not get them to work though it did lead me to a very nifty Macro

Sub PrintCopies()
Dim i As Integer
Dim iCopies As Integer
iCopies = Application.InputBox("How many copies?")
For i = 1 To iCopies
Range("d4").Value = Range("d4").Value + 1
ActiveSheet.PrintOut
Next i
End Sub


Thanks go to Bernie Deitrick of PC Review for this one and a purple pointy thing for Geoff for sending me off in the right direction (I prefer pointers not answers these days as I feel more accomplished now)


Regards, Phil.

http://www.manchester-medical-services.co.uk