I have created a Purchase Order form in excel. When the employees use the
template I would like to have the form autonumber. Is this possible?
Thanks
Tracy

hi,
here is a auto number macro that i wrote sometime back for someone else. but
you can have it too. be sure to read all the comments laced through out the
code.
you will have to do so editing to make it fit your template. if it's not
what you want, post back tomorrow and i'll see if i can fix it.
NOTE: make a back up copy of your template BEFORE you run the macro the
first time.
Regards
FSt1

Sub MacCreatePO()

'Written by FSt1 on or about 3/14/05.

'Creates a new PO(purchase order) workbook, saving the created PO workbook
with
'the PO number as part of the file name. Leaves the "template" PO workbook
"as is".
'macCreatePO should be triggered from a button on the PO Template sheet.

Dim n As Range
Set n = Range("A1") ' the all important PO number.
'Edit PO number address to the place on your PO
template.
Application.CutCopyMode = False 'clears the clipboard
Range("A1:L25").Select 'edit to accommodate the size of your PO template
Selection.Copy
Workbooks.Add
Range("A1").PasteSpecial xlPasteAll
'Edit the file path to where you want to save the PO workbook on your PC
or
'network
ActiveWorkbook.SaveAs Filename:="H:\CodeStuff\PO" & n & ".xls", _
FileFormat:= xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWorkbook.Close
Cells(1, 1).Select 'go and park at cell A1 and wait
MsgBox ("Purchase Order was saved as PO" & n & ".xls")
n.Value = n.Value + 1 'add 1 to old PO number for new PO number
'change the place of the date to add. Macro puts today's date in but
'this can be edited.
Range("I1") = Date
'clear the contents of the old PO to set up the next PO. clear
surgically so to leave
'template in tact. more clearContents commands may be needed.
'remember...this is a surgical delete. only the data and not the
template.
'the line below should be edited to fit your data.

Range("C5,C6,C8,C9,C10,E5,E6,E8,E9,E10,G5,G7,G8,G9 ,G10,F12,D13,G13").ClearContents
ActiveWorkbook.Save 'save the cleared template workbook with new(next) PO
' number. Ready for next PO
End Sub


"Tracy" wrote:

I have created a Purchase Order form in excel. When the employees use the
template I would like to have the form autonumber. Is this possible?
Thanks
Tracy

Do a Google newsgroup search. There are 2000 hits from 1 Jan 2000.
http://groups.google.ca/groups?as_q=... 2005&safe=off
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Tracy" wrote in message
...
I have created a Purchase Order form in excel. When the employees use the
template I would like to have the form autonumber. Is this possible?
Thanks
Tracy