+ Reply to Thread
Results 1 to 19 of 19

Macro Autonumbering

  1. #1
    Registered User
    Join Date
    03-31-2014
    Location
    Waco, Tx
    MS-Off Ver
    Excel 2013
    Posts
    12

    Macro Autonumbering

    Hi guys, I know there have been previous posts with code to have an invoice save and automatically open a new one with the next number. I have been having problems getting it to work.

    Here's the run-time error I get "1004"

    Run-time error.jpg

    And here's my code:
    Please Login or Register  to view this content.



    I just want to be able to save an invoice, and as I save it it automatically saves the invoice number and opens the next invoice number clear and ready to fill in.

    Thanks!
    Last edited by brazilianch; 03-31-2014 at 03:30 PM. Reason: Code

  2. #2
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Macro Autonumbering

    Please take a moment to read the forum rules especially pertaining to rule #3.

    3. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the [#] button at the top of the post window (if you are editing an existing post, press Go Advanced to see the [#] button). The result will appear like this in the post window:

    [code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code]

    ... and appear like this when posted:


    Please Login or Register  to view this content.

    What ranges need cleared?
    Thanks,
    Solus


    Please remember the following:

    1. Use [code] code tags [/code]. It keeps posts clean, easy-to-read, and maintains VBA formatting.
    Highlight the code in your post and press the # button in the toolbar.
    2. Show appreciation to those who have helped you by clicking below their posts.
    3. If you are happy with a solution to your problem, mark the thread as [SOLVED] using the tools at the top.

    "Slow is smooth, smooth is fast."

  3. #3
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,966

    Re: Macro Autonumbering

    It's an option to store the sequence outside the workbook.

    Please Login or Register  to view this content.
    In the example, the text file is preloaded with the value 1 and each time the code runs, the value is retrieved and the number incremented by one for next time.
    Martin

    If my solution has saved you time and/or money, please consider sponsoring my run in the 2020 London Marathon in aid of Cancer Research UK.

    https://uk.virginmoneygiving.com/MartinRice

  4. #4
    Registered User
    Join Date
    03-31-2014
    Location
    Waco, Tx
    MS-Off Ver
    Excel 2013
    Posts
    12

    Re: Macro Autonumbering

    Sorry about the unorganized code; it should be fixed now.
    I need B9:E9 and F5 Cleared, that's just the item, description, quantity, unit price.

    (I just recognized and fixed the "B9:F9" and changed it to "B9:E9")

  5. #5
    Registered User
    Join Date
    03-31-2014
    Location
    Waco, Tx
    MS-Off Ver
    Excel 2013
    Posts
    12

    Re: Macro Autonumbering

    @mrice Will this change the number each time I open the file? So if I open one and don't need it, or don't save it, I lose a number in my invoices?

  6. #6
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,966

    Re: Macro Autonumbering

    You could incorporate the code into a larger routine including your save if it is important to you not to have any gaps in the sequence

    E.g.

    Please Login or Register  to view this content.

  7. #7
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Macro Autonumbering

    Try pasting this to you workbook module:
    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    03-31-2014
    Location
    Waco, Tx
    MS-Off Ver
    Excel 2013
    Posts
    12

    Re: Macro Autonumbering

    @SolusRankin That code works great! I saves the file as whatever the invoice number is and clears the data. The only thing left though is auto-numbering so when I either open or save it the next numerical number auto populates. How would I do this?

  9. #9
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Macro Autonumbering

    That code will automatically increment F6 when you save.
    Please Login or Register  to view this content.
    This will increment when you open. Change "YourSheetName" to the name of the sheet that you want to increment.

  10. #10
    Registered User
    Join Date
    03-31-2014
    Location
    Waco, Tx
    MS-Off Ver
    Excel 2013
    Posts
    12

    Re: Macro Autonumbering

    Okay so when I hit the save button I assigned the macro to, It appears to open a new workbook and close one, the information doesn't clear, and the number doesn't change? It is saving the page as the invoice number though.

  11. #11
    Registered User
    Join Date
    03-31-2014
    Location
    Waco, Tx
    MS-Off Ver
    Excel 2013
    Posts
    12

    Re: Macro Autonumbering

    Here's the entire code I've copied:

    Please Login or Register  to view this content.

  12. #12
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Macro Autonumbering

    You don't have to assign those macros to anything. Just hit the save button or go to file--> save.

  13. #13
    Registered User
    Join Date
    03-31-2014
    Location
    Waco, Tx
    MS-Off Ver
    Excel 2013
    Posts
    12

    Re: Macro Autonumbering

    When I do this, all that happens is the original file is saved. Attached is the workbook if you would like to see.
    Attached Files Attached Files

  14. #14
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Macro Autonumbering

    The code needs to be placed in the workbook module, not a stand alone module. Please see attached: Solus Edit.xlsm

    Please note. There were a couple other issues I found that never would have been caught without an example workbook. Posting your workbook is always the best way to get a complete answer.

  15. #15
    Registered User
    Join Date
    03-31-2014
    Location
    Waco, Tx
    MS-Off Ver
    Excel 2013
    Posts
    12

    Re: Macro Autonumbering

    Awesome thanks a lot! It works great!

  16. #16
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Macro Autonumbering

    Glad to help. And welcome to the forum.

    If you are happy with the solution please mark the thread as [SOLVED] using the thread tools at the top.

    And on any thread you can show appreciation to those who have helped you by clicking the * below their post.

  17. #17
    Registered User
    Join Date
    03-31-2014
    Location
    Waco, Tx
    MS-Off Ver
    Excel 2013
    Posts
    12

    Re: Macro Autonumbering

    Okay thanks again, one quick final question, how do I go about changing the original file and saving it?

  18. #18
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Macro Autonumbering

    I placed a macro in the last example called SaveForDeveloper. Run it and it will ask you want you want to call it and where you want to save it. It will then save it for you without running the other macro.

  19. #19
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Macro Autonumbering

    If you are happy with the solution please mark the thread as [SOLVED] using the thread tools at the top.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 03-27-2009, 04:35 PM
  2. Add to Autonumbering macro!
    By Spellbound in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-01-2008, 06:35 PM
  3. Autonumbering
    By jonnygrim in forum Excel General
    Replies: 3
    Last Post: 07-21-2008, 03:43 PM
  4. [SOLVED] autonumbering
    By Lino in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-06-2005, 01:05 AM
  5. autonumbering
    By Lino in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-05-2005, 11:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1