+ Reply to Thread
Results 1 to 22 of 22

Trying to autonumber

  1. #1
    Registered User
    Join Date
    08-19-2013
    Location
    Oklahoma
    MS-Off Ver
    Excel 2010
    Posts
    11

    Trying to autonumber

    I am trying to autonumber an packing list and I'm not having any luck. I used some code that I found but I am really very new at this. I've been using Excel for years but not VBA.
    I grabbed the code from another spreadsheet that someone made and I thought it would work. The print works great but the invoice number doesn't change?
    I updated my file path in the code as well. I would also like to save on close the the new packing list number as the file name such as packingList6354
    If anyone wants to take a look at it and the sample the help would be greatly appreciated!
    https://www.dropbox.com/s/jznrxrt6fz...%20%282%29.zip
    Last edited by nonstopcrunchy; 08-27-2013 at 03:00 PM. Reason: Solved

  2. #2
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: Trying to autonumber

    Hey Crunchy,

    You can download right on this site - go to advanced and click on the paper clip - some of us won't open a dropbox!
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  3. #3
    Registered User
    Join Date
    08-19-2013
    Location
    Oklahoma
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Trying to autonumber

    Ah, Thanks!
    Attached Files Attached Files

  4. #4
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Trying to autonumber

    Hi nonstopcrunchy

    This Code is in the attached. It's Code written by Harald Staff (Microsoft MVP) with slight amendments (his disclaimer). I've modified it to do as it seems you require.

    Please follow these set up instructions:

    First download the File and save it. Change the File Extension from .xlsx to .xltm (the Forum would not allow me to upload a Template File).

    Before you start on the newly named file, you need to setup the following:

    1. Use Notepad (Start menu/Programs/Accessories/Notepad), and create a new file called "PListNo".
    2. Put your starting Packing List Number (simply type in the Number) in "PListNo".
    3. Select a directory and save "PListNo" as a text file.
    4. Put the following macro in your Workbook Open Event (it'll already be there...so no need to do step 4). Save the template.
    5. Set up a directory to save your invoices. The macro is using:
    ActiveWorkbook.SaveAs MyPath & "PackingList" & ActiveSheet.Range("InvNo") & ".xlsx", FileFormat:=51 'Change to suit

    In this Code you'll see two sets of Hash Marks (#####)...these are the lines of Code you need to change to suit your environment.

    Here's what happens. When you open the Template the Next Number from PListNo.txt is entered into Range("InvNo"). The File is Saved As "PackingList" & ActiveSheet.Range("InvNo") & ".xlsx".

    Let me know of issues.
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by jaslake; 08-20-2013 at 06:17 PM.
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

  5. #5
    Registered User
    Join Date
    08-19-2013
    Location
    Oklahoma
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Trying to autonumber

    The code and directions were perfect. The auto-numbering worked like a charm! The on open is great. Thank you. I thought I had the print function down but I ran into a hiccup. I can have button print without any problem but I need multiple page lists (not sheets,) The lists can be anywhere from 1 - 50 pages. I recorded a macro to copy the list down whenever they need a new one but the pages always mess up and get out of proper page spacing so that I can't print with a header? I wanted it to clear the contents from B13:AB32 and copy it down a page so that I could use a header that says Packing List Sheet # of #. I wanted to assign a macro to copy the list, with the top part filled out to the next blank page below so it could print. The other button was just going to be to print. I'm not sure if part of the problem was different margins using different printers too?
    I tried making them new sheets in the workbook but then I couldn't get the headers to number. That is what the two shapes were for, I hadn't made them buttons yet.
    Last edited by nonstopcrunchy; 08-26-2013 at 12:50 PM.

  6. #6
    Registered User
    Join Date
    08-19-2013
    Location
    Oklahoma
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Trying to autonumber

    Accidental double post
    Last edited by nonstopcrunchy; 08-26-2013 at 12:57 PM. Reason: Accidental double post

  7. #7
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Trying to autonumber

    Hi nonstopcrunchy

    How about mocking up what you're after...perhaps one of us can help.

  8. #8
    Registered User
    Join Date
    08-19-2013
    Location
    Oklahoma
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Trying to autonumber

    Here is the layout. It uses the code from jaslake wrote. I hope it makes more sense now. They will start with the single page and then create more pages as needed. I just wrote the header on the sheet to illustrate how it will look when it is printed instead of you having to look at the header. Also, like jaslake showed me, I made it a .xlsx and it will need to have the file extension changed back to .xltm to work. The biggest problem was that the pages seem to migrate down as it copies so I can't print them without adjusting them manually which isn't feasible.

    Thanks for the help!
    Attached Files Attached Files
    Last edited by nonstopcrunchy; 08-26-2013 at 03:15 PM.

  9. #9
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Trying to autonumber

    Hi nonstopcrunchy

    I don't see this Code in your File
    I recorded a macro to copy the list down whenever they need a new one
    The Code may not do as you require but it may give us a clue as to what you desire...please include it in your upload.

  10. #10
    Registered User
    Join Date
    08-19-2013
    Location
    Oklahoma
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Trying to autonumber

    I didn't include that macro because it was broken but I can in a bit. It was just clear the contents and copy the paste on the page below.

  11. #11
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Trying to autonumber

    Hi nonstopcrunchy

    The key (for me) is gaining insight into your requirements
    The Code may not do as you require but it may give us a clue as to what you desire...please include it in your upload.

  12. #12
    Registered User
    Join Date
    08-19-2013
    Location
    Oklahoma
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Trying to autonumber

    Thanks, that makes perfect sense.
    I put it back in. At first, I wanted to have a button on the bottom of each page that the user, who is almost completely new to excel, could click and create a new blank list on a new page that they could continue on. I also have the macro in to create a new sheet with a list ready to go for data entry. I really think I would like the second one better if it is possible to have a header that says "Packing List Sheet 1 of 4" where it is sheet number of sheet number. This would really help me to eliminate any printer issues with roaming page breaks etc on a 50 page document when printing. Copying the sheet lets me absolutely avoid any formatting issues since paste special isn't foolproof. I have attached the file.
    I did find a cool trick that if I name the first sheet "Packing List (1)" then any newly created sheet is named "Packing List (2)" etc. Then I can insert the sheet name in the header and it works great. I can't get the number of sheets for it though.
    I found nifty code to count them but it only displays a message box:

    Sub shtcount()
    MsgBox "# of sheets: " & Sheets.Count
    End Sub

    Thank you so much for the help. You have been great!
    Attached Files Attached Files
    Last edited by nonstopcrunchy; 08-27-2013 at 08:23 AM.

  13. #13
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Trying to autonumber

    Hi nonstopcrunchy

    This Code has been modified to do this
    a header that says "Packing List Sheet 1 of 4" where it is sheet number of sheet number
    Please Login or Register  to view this content.
    The Code has been assigned to the Button. As before, rename the File Extension.
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    08-19-2013
    Location
    Oklahoma
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Trying to autonumber


    Thank you so much! That was perfect. I am also very excited that I can look at it and start to learn more using this. I would have added more reputation but it said I have to spread it around more before giving you any more.

  15. #15
    Registered User
    Join Date
    08-19-2013
    Location
    Oklahoma
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Trying to autonumber

    One quick question, when recorded a macro to print the entire workbook, selected the printer it started printing page 1 of 20561! with the header on each sheet. IS there something I need to add?
    Last edited by nonstopcrunchy; 08-27-2013 at 02:22 PM.

  16. #16
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Trying to autonumber

    Don't know...post the Code and I'll look at it. Please to be sure to use Code Tags.

  17. #17
    Registered User
    Join Date
    08-19-2013
    Location
    Oklahoma
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Trying to autonumber

    It was printing the header "packin List Sheet 55 of 20561" etc. Maybe it was the way I recorded the macro.

    Please Login or Register  to view this content.

  18. #18
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Trying to autonumber

    Look at your worksheets in Page Break View...

  19. #19
    Registered User
    Join Date
    08-19-2013
    Location
    Oklahoma
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Trying to autonumber

    Wow, I just did that. I just set my print area to the first page and it works. I'm not sure if the macro paginated those or not. Thanks for your massive amount of help!
    I'm marking this thing solved and I'm very pleased that I found this forum and your help.

  20. #20
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Trying to autonumber

    You're welcome...glad I could help.

    I've not looked but I'm guessing that carries over from the Template...look there.

  21. #21
    Registered User
    Join Date
    11-15-2012
    Location
    Denmark
    MS-Off Ver
    Excel 2003
    Posts
    45

    Re: Trying to autonumber

    I tried to follow your instructions, but got an error message I couldn't decifer... It's something about the file format or file extension not being valid. I use Excel 2003

  22. #22
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Trying to autonumber

    Hi DKAbi

    You'll need to take your response back to your OWN Thread.

+ 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. [SOLVED] Autonumber
    By nik7 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-23-2013, 06:50 PM
  2. How to update an autonumber using vba
    By hvisa in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-15-2009, 08:45 PM
  3. Autonumber
    By jonnygrim in forum Excel General
    Replies: 2
    Last Post: 07-17-2008, 05:22 PM
  4. Can Excel AutoNumber
    By Globe Director in forum Excel General
    Replies: 2
    Last Post: 03-24-2006, 08:20 AM
  5. autonumber
    By Hardy in forum Excel General
    Replies: 4
    Last Post: 11-24-2005, 01:45 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