Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: unique number

  1. #1
    New Member
    Join Date
    Feb 2002
    Location
    Illinois USA
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I am trying to make a template that can give each new spreadsheet a unique number ie. Work Order 1, Work Order 2, etc. How can I make this either on open or with an assigned macro button?

  2. #2
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,168
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sometimes, a simple approach is best. This question can be answered two ways in my opinion.

    The first way (simple) is to have you enter the text "Work Order #" (without the quotes) in cell A1 of sheet1.

    Then, paste this macro into a standard module, or copy & paste only the one line
    Sheet1.[B1] = Sheet1.[B1] + 1
    into a Workbook_Open event module:

    Sub Auto_Open()
    Sheet1.[B1] = Sheet1.[B1] + 1
    End Sub

    Every time you open your workbook, A1 and B1 will look like
    Work Order # 1
    and then
    Work order # 2, etc, incrementing upward by one.

    The more complicated solution would be to write code that takes into account the text "Work Order #" if you want that with the actual number in one cell. The code is not difficult, just more complicated-looking for your co-workers who may have to modify the file in your absence.

    This first simple option is more intuitive for people to understand, and needs no adjustment, if for example your text changes from "Work Order #" to "Invoice Number".

    If you need a different solution that what I gave here, please repost.

    Tom Urtis

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Georgia USA
    Posts
    569
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I read the post wrong, I thought you wanted to save the file with a new number. Anyway..

    It want work as a template, maybe someone can fix it, but maybe it will help, put your first work order number in AA1, put Work Orders in AC1 the Marco will save the workbook by the name in AC1 and the number in AB1, AB1 will change by one number every time the Marco is run. Assign the Marco to a button if you want, also you can change the cell reference if you need to, if you want it to run when the workbook is opened just put Application.Run " Save_By_Number " in this workbook code. Hope this helps Paul B

    Sub Save_By_Number()
    Application.ScreenUpdating = False
    lastnumber = Worksheets("sheet1").Range("AA1").Value
    nextnumber = lastnumber + 1
    Worksheets("sheet1").Range("AB1").Value = nextnumber
    Worksheets("sheet1").Range("AA1").Value = nextnumber
    FileName = Worksheets("sheet1").Range("AC1").Value & Worksheets("sheet1").Range("AB1").Value
    ActiveWorkbook.SaveAs (FileName)
    Application.ScreenUpdating = False
    End Sub


    [ This Message was edited by: Paul B on 2002-02-18 20:27 ]

  4. #4
    New Member
    Join Date
    Feb 2002
    Location
    Illinois USA
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks guys, between the two posts, I have corrected the problem

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
  •