Autonumber a form

[jray]

I have a basic spreadsheet I want to use as an invice form.

Can I print multiple copies and have each copy incrementally number itself, so the first one is 100, the second copy is 101 etc and then save that so that the next time I print a bunch I do not duplicate an invoice already printed.

Thanks.

 

[Smitty]

Here's a start:

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> PrintInvoices()<br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>        <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> Application.InputBox("How many copies do you want to print?", "Print Invoices", 1, Type:=1)<br>            <SPAN style="color:#00007F">With</SPAN> ActiveSheet<br>                Range("A1").Value = i<br>                .PrintPreview <SPAN style="color:#007F00">' Change to .PrintOut after testing</SPAN><br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> i<br>        ActiveWorkbook.Save<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

HTH,

 

[jray]

Sorry, I am not sure what I should do with what you posted.

But I sure do appreciate the speedy reply!!

 

[Smitty]

No problem,

Hit ALT+F11 to open the VBE (Visual Basic Editor). When that opens goto Insert-->Module. Paste the code in the new window that opens on the right, then ALT+Q to exit back to Excel.

Back in Excel goto View-->Toolbars-->Forms & draw a Button on your sheet-->Once done you'll get a menu-->Select "Assign Macro" and select "Print Invoices", then OK.

Once you've done that you can hit the button and it will fire the code.

 

[jray]

Thanks sooo much!!! I will push it just a bit!!!

Can Excel keep track of the number rather than having it set back to 1?

Thanks for all your help!!!

 

[Smitty]

Can Excel keep track of the number rather than having it set back to 1?

Sure:

For i = Range("A1").Value to ...

Although you might want to add a qualifier. I.E. If LenB(Range("A1").Value) = 0 then Range("A1").Value = 1

Just to make sure you have a starting value other than "".

 

[jray]

Again, your response is most appreciated, though not understood....

I thank you for your efforts, but I do not understand VBA, I did try to paste the line you gave me into the macro but it did not do anything. I am not sure where it should have gone etc.

I did learn from what you had sent and I really appreciate your time and efforts.

I think it is time for me to learn some VBA!!!

 

[Smitty]

Actually if you're trying to increment the number in A1 (assuming that's where your invoice # is) you'd use this:

Range("A1").Value = Range("A1").Value + 1

Instead of Range("A1").Value = i

 

[jray]

Wow!!!

Thanx!! It works great!!!

Thank you so much for your time and efforts!!!