Automatic Formula or Macro

[Jessica]

Hello all. I have created purchase order system that I would like other
employees to use. What I would like to happen is that every time the PO
workbook opens the number in "L1" in "Sheet1" automatically increases by one.
So if I save PO 00001. The next person who opens it will autimatically get
PO 00002. How do I accomplish this? Thanks for your help!

 

[Bernie Deitrick]

Jessica,

In the Workbook open event, use code like this:

Private Sub Workbook_Open()
Worksheets("Sheet1").Range("L1").Value =
Worksheets("Sheet1").Range("L1").Value +1
Application.DisplayAlerts = False
Thisworkbook.Save
Application.DisplayAlerts = True
End Sub

I'm assuming that the number is actually a number and is not a string like
"PO #00001" If you need a string like that, use a custom format like

"PO #"00000

and only enter the number 1 into cell L1.

HTH,
Bernie
MS Excel MVP

 

[Loris]

You could do it with an Autpen macro that does the following:
Let's say your PO is on sheet 1 and sheet 2 A1(or whatever cell you want)
contains the number of the current PO. Your macro will go to the cell on
sheet 1 containing the PO no. and enter the formula =sheet2!a1+1, then copy
that cell and paste over it using Paste Special, Values. Then copy the
contents of that cell and paste it to sheet2 A1. Saving the macro under the
name Autpen will cause the macro to automatically run whenever the file is
opened and thus give you a new and sequential PO number. If you need to do
more than one PO during a session, you can simply run the macro as you would
any other macro, through the use of a short-cut key, toolbar button, or the
menu.