Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Automatic Formula or Macro
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! -- Jessica |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Automatic Formula or Macro
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 "Jessica" wrote in message ... 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! -- Jessica |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Automatic Formula or Macro
You could do it with an Auto_Open 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 Auto_Open 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. "Jessica" wrote: 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! -- Jessica |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatic Startup for a Macro | Excel Discussion (Misc queries) | |||
Automatic run of Macro | Excel Discussion (Misc queries) | |||
Automatic run of Macro | Excel Discussion (Misc queries) | |||
MACRO TO SET THE AUTOMATIC CALCULATION | Excel Discussion (Misc queries) | |||
Macro for Automatic Subtotals | Excel Discussion (Misc queries) |