Results 1 to 5 of 5

Thread: Automatic Sequential Numbers
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jun 2004
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Automatic Sequential Numbers

    I'm really new to the macros in excel, but I'm looking for something that will allow me to automatically generate a sequential number that can be used for an invoice or PO number. Once it uses the number it will go to the next number. I hope I'm clear in what I need, basically I have a PO form and everytime I open up a new form I want a new number in PO # box on the form. Could you also please either tell me or link me how to set-up the macro to get it working. Thanks!

  2. #2
    MrExcel MVP Smitty's Avatar
    Join Date
    May 2003
    Location
    Redmond, WA
    Posts
    29,535
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Automatic Sequential Numbers

    Welcome to the Board!

    The following code will increment an existing number by 1 each time the workbook is opened. If you're starting blank, then it will automatically start at 1001. The code goes in the "ThisWorknbook" module.

    Private Sub Workbook_Open()
    Dim rng As Range
    ' Set the range for PO # - Adjust Sheet & Range as necessary
    Set rng = Sheets("Sheet1").Range("A2")

    If rng = "" Then
    ' Set initial PO#
    rng = 1001
    ' Increment PO # by 1
    Else: rng.Value = rng.Value + 1
    End If

    End Sub


    Hope that helps,

    Smitty

  3. #3
    New Member
    Join Date
    Jun 2004
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Automatic Sequential Numbers

    It helps because now I have the code, but I have no idea how to set it up. Any suggestions??

  4. #4
    Board Regular Iridium's Avatar
    Join Date
    Jul 2002
    Location
    Walsall, England
    Posts
    2,831
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Automatic Sequential Numbers

    To use the code whilst in xl hit Alt+F11. On the left hand side you should see the project explorer window (if not hit Ctrl+R). You should see your workbook listed as VBAProject (yourworkbook name) - double click on the ThisWorkbook module. Paste the code into this.

    HTH
    Iridium


  5. #5
    MrExcel MVP Smitty's Avatar
    Join Date
    May 2003
    Location
    Redmond, WA
    Posts
    29,535
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Automatic Sequential Numbers

    Hit ALT+F11 to open the VB Editor. On the left hand pane, you'll see a folder called VBAProject(Filename.xls). Expand it and double-click on the module called "ThisWorkbook". Paste the code in the window that opens on the right. Then change this: Sheets("Sheet1").Range("A2") to reflect the specific sheet name and range where you want the PO number stored.

    You can hit F5 to test the code.

    ALT+Q to exit VBA and return to your workbook.

    The next time you open the workbook, the PO # will increment by 1.

    Hope that helps,

    Smitty

    EDIT: Iridium beat me to it (What's up Bud?)

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •