Ask Me Help Desk

Ask Me Help Desk (https://www.askmehelpdesk.com/forum.php)
-   Spreadsheets (https://www.askmehelpdesk.com/forumdisplay.php?f=395)
-   -   Auto Sequential Numbering (https://www.askmehelpdesk.com/showthread.php?t=565156)

  • Mar 25, 2011, 07:26 AM
    BertG
    Auto Sequential Numbering
    Is there a way within Excel to Auto Generate a new number every time I open a spreadsheet.ie: I made a Purcahse Order template and I would like for Excel to generate a new Purchase Order number whenever I open it up. Can anyone let me know if this is a possibility.
  • Mar 26, 2011, 04:16 AM
    BrookW97
    I don't know how to do every time it opens, but you can do this by recording a Macro. If you aren't familiar with Macros, it's a way to record an action or series of actions. Basically, once you've recorded the Macro and told it what to do... you will just have a button in excel to click on every time you want a new invoice number.
    I think for what you want, you will have to do in visual basics. I'm not very good at those but office has some step by step instructions. Search "Create a macro by using Microsoft Visual Basic" in HELP. (you will probably have to add the developer tool bar but this is all in step by step)
    when it mentions code---
    Try to use a code similar to this and paste just before sub: Range("D3").Value = Range("D3").Value + 1
    I know you can also insert an object (button) and assign the macro to that button.

    This is all above my head though--but maybe it will get you pointed in right direction.
    good luck!
  • Mar 26, 2011, 05:02 AM
    ScottGem

    This is better done in a database, but is possible in a spreadsheet. But to help you we need to know more about the structure of your template. If it is, in fact a template (xlt file) then you would need to read the number from the last PO you saved since you can't save the template. So more info is necessary.
  • Mar 28, 2011, 05:01 AM
    BertG
    Thanks for your response. I did find a way to do it in VBE. With the following code:
    Sub auto_open()
    On Error Go to ErrorCatch
    Cells(9, 13) = Cells(9, 13) + 1
    If Cells(9, 13) = 10000 Then Cells(9, 13) = 0
    ErrorCatch:
    End Sub

    It works great. I get a new p.o. # every time I open the Purchase order file.

  • All times are GMT -7. The time now is 11:14 PM.