+ Reply to Thread
Results 1 to 14 of 14

Use spreadsheet to autofill product identification card template.

  1. #1
    Registered User
    Join Date
    06-07-2013
    Location
    Minnesota
    MS-Off Ver
    Excel 2010
    Posts
    12

    Use spreadsheet to autofill product identification card template.

    [SOLVED]Attachment 241540

    I am trying to find a formula that will enter the info from the cells on the spreadsheet into the Product ID card template text boxes.

    So for A54 or A55 I would like those inserted into both Text Box 1 and Text Box 12. B54 or B55 would go to Text Box 2 and Text Box 11. C54 or C55 would go into Text Box 3 and Text Box 13. And so on so forth for each column along the row for that part.

    For the perfect functionality, I would like to be able to add each row of info for each part, enter all the ones I need to do, then have them all print out in the template one by one. At this point I have to do each one manually, and this could potentially save me hours.

    Please give me some ideas if you have them.

    Thanks,

    Craig
    Last edited by Pontooner; 06-12-2013 at 09:41 AM.

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,462

    Re: Use spreadsheet to autofill product identification card template.

    Here's one suggestion.
    Attached Files Attached Files
    Last edited by protonLeah; 06-08-2013 at 12:13 AM.
    Ben Van Johnson

  3. #3
    Registered User
    Join Date
    06-07-2013
    Location
    Minnesota
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Use spreadsheet to autofill product identification card template.

    I like the idea, I would probably want to change that the sheet 2 and 3 are together so they print as one page that I can fold in half so it's dual sided. I'm not seeing it autofilling the template though. Is that a possibility? Thanks for the suggestion!

  4. #4
    Registered User
    Join Date
    06-07-2013
    Location
    Minnesota
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Use spreadsheet to autofill product identification card template.

    Don't know how to delete these.
    Last edited by Pontooner; 06-10-2013 at 06:29 PM.

  5. #5
    Registered User
    Join Date
    06-07-2013
    Location
    Minnesota
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Use spreadsheet to autofill product identification card template.

    Quote Originally Posted by protonLeah View Post
    Here's one suggestion.
    Re: Use spreadsheet to autofill product identification card template.

    Ok I was just toying around with that, and saw that it does autofill when You press the print button, but how is it autofilling? I would have to have the PWB CARD and the Prod Release Req sheets consolidated as one card that prints as one sheet that folds down the middle. Is it just pulling the values automatically with no formula?

  6. #6
    Registered User
    Join Date
    06-07-2013
    Location
    Minnesota
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Use spreadsheet to autofill product identification card template.

    KanBan-whole card-bvj.xlsmLeah, I changed the template out a little on the 3rd worksheet labeled "prod release req." That is what I would like the datasheet to print out as, the second worksheet works great, but only prints half of the card. I also added one more column to the Data Sheet, "Warehouse Location." But I would only really need to use the Data sheet and Prod release req sheet set up as it is. I wouldn't need the backup or the pwb card. I tried going into the macro to see how you had it set up, but I am sure if I started changing things it would never work. I really appreciate the input!
    Last edited by Pontooner; 06-10-2013 at 02:08 PM.

  7. #7
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,462

    Re: Use spreadsheet to autofill product identification card template.

    I rewrote the the macro so you can see how it moves the data from the database sheet to the printout sheet easier.

    Please Login or Register  to view this content.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    06-07-2013
    Location
    Minnesota
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Use spreadsheet to autofill product identification card template.

    Quote Originally Posted by protonLeah View Post
    you can see how it moves the data[/CODE]
    I can definitely see how it is moving it, but not sure how to build that yet.

    I am getting a "Subscript out of range (Error 9)" Message when I try to print. I hit debug and it goes into Module 1 and highlights, "Set PWBCARD = Sheets("pwb card")" I figured since pwb card is no longer a sheet on this, I would change it to prod release req, but that didn't work. It seems like Module 1 is meant for the last set-up and not this new template. Sorry if what I'm missing is super elementary, I am admittedly a noob.

  9. #9
    Registered User
    Join Date
    06-07-2013
    Location
    Minnesota
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Use spreadsheet to autofill product identification card template.

    After changing what was in quotes from "pwb card" to "prod release req,"it now gives me the error 9 at "PWBCARD.Range(DataPointer(Ndx2)).Value = DataVals(Ndx2)" So, it seems like changing to "prod release req" did something right, but now I'm not sure what is logical to get past this wall.

  10. #10
    Registered User
    Join Date
    06-07-2013
    Location
    Minnesota
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Use spreadsheet to autofill product identification card template.

    KanBan-whole card-bvj-2.xlsm

    Leah, I think I figured it out. Wow VBA is pretty awesome, I'm going to have to keep working with it for sure. Please check my additions to your work and let me know if you see any problems. Thank you so much!

  11. #11
    Registered User
    Join Date
    06-07-2013
    Location
    Minnesota
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Use spreadsheet to autofill product identification card template.

    Now this is weird, in Column F if I just have one number to put, it comes up as a date when it prints. I have some examples in the data sheet. KanBan Autofill PBW.xlsm How do I get it to quit printing with the jan-5 and just have it print a number whether it's 1,2,3,20, 25?

  12. #12
    Registered User
    Join Date
    06-07-2013
    Location
    Minnesota
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Use spreadsheet to autofill product identification card template.

    Ok the F column wasn't the problem, the box on the template was marked as a custom-date, so I changed those to general and now they are printing just fine, issue averted there.

    One more question, is it possible to have the macro create an auto save file with the PBW part# as the filename? This has been a real crash course in VBA, definitely going to be learning more about it.

  13. #13
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,462

    Re: Use spreadsheet to autofill product identification card template.

    is it possible to have the macro create an auto save file with the PBW part# as the filename
    You want the printed card saved as a new workbook?
    Please Login or Register  to view this content.
    Last edited by protonLeah; 06-12-2013 at 12:30 AM.

  14. #14
    Registered User
    Join Date
    06-07-2013
    Location
    Minnesota
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Use spreadsheet to autofill product identification card template.

    Yeah, I was thinking that a backup would be good in case my warehouse loses one. I'll have to check it out tomorrow morning, I only have an Excel trial at home right now, so I can't do anything with the macros. Working on getting a laptop supplied one of these days. Thank you again for all the help!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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