+ Reply to Thread
Results 1 to 27 of 27

Print labels for items received on receiving report

  1. #1
    Valued Forum Contributor
    Join Date
    02-26-2010
    Location
    Chattanooga, TN
    MS-Off Ver
    Excel 2003/2007/2010/2016
    Posts
    432

    Talking Print labels for items received on receiving report

    Hello!

    Before I try to tackle this new project, I wanted to check with you guys/gals for setup guidance.

    1. Warehouse receives parts from suppliers.
    2. Warehouse inspects each part and documents part info and how many are acceptable and how many are unacceptable.
    3. Warehouse (now) hand-writes a paper tag with the same information that is on the Receipt Log and affixes it to the items.

    Notes:

    1) For small items (nuts, bolts), one tag could be affixed to cover 20 items in a Ziplock bag. So qty of items does not necessarily correspond to qty of tags.

    2) 3 tags (Accepted, Hold, uNacceptable). A-tags are always by themselves. If an H or N tag is needed, an A tag needs to be printed with it.

    Bottom Line: We would like to automate printing of tags using the information that has already been entered in the Receipt Log.

    I was thinking of doing it this way:

    1) User selects rows to copy.
    2) Button copies rows to other sheet.
    3) Mail merge to Word from other sheet.
    4) Word prints to labels.
    5) Peel off labels and stick to paper tags.

    Other problems: If the user wants to just print one tag, how can the user tell the printer which location of an Avery label sheet gets that tag?

    I started a workbook which has a tag template (more-or-less) on it.

    Anyone know of anything out there already like this? Any recommendations from the forum on how to set this up?

    ??

    Lost
    Attached Files Attached Files

  2. #2
    Forum Expert Mordred's Avatar
    Join Date
    07-06-2010
    Location
    Winnipeg, Canada
    MS-Off Ver
    2007, 2010
    Posts
    2,787

    Re: Print labels for items received on receiving report

    This looks like fun. You would like the whole selected row to be put on one label?
    If you're happy with someone's help, click that little star at the bottom left of their post to give them Reps.

    ---Keep on Coding in the Free World---

  3. #3
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Print labels for items received on receiving report

    Hi Learning,

    You discribed the direction I would be working. Mailmerge into Word using Label templates would be my first choice.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  4. #4
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Print labels for items received on receiving report

    Other problems: If the user wants to just print one tag, how can the user tell the printer which location of an Avery label sheet gets that tag?
    Add as many empty records before filled records as labels have to be bypassed.



  5. #5
    Valued Forum Contributor
    Join Date
    02-26-2010
    Location
    Chattanooga, TN
    MS-Off Ver
    Excel 2003/2007/2010/2016
    Posts
    432

    Smile Re: Print labels for items received on receiving report

    Mordred:

    Some of the columns on the sheet are used for signatures, etc, but the data in most of them are going to go on the labels.

    Hand-writing labels takes alot of time per shipment, so this will be hundreds of man-hours that could be spent elsewhere if this project works out.

    Respectfully,

    Lost

  6. #6
    Valued Forum Contributor
    Join Date
    02-26-2010
    Location
    Chattanooga, TN
    MS-Off Ver
    Excel 2003/2007/2010/2016
    Posts
    432

    Re: Print labels for items received on receiving report

    All,

    Progress....

    1) User selects rows to copy: (Done)
    2) Button copies rows to other sheet: (Done).
    3) Mail merge to Word from other sheet: .(Trying to figure this one out.)



    Lost
    Attached Files Attached Files

  7. #7
    Valued Forum Contributor
    Join Date
    02-26-2010
    Location
    Chattanooga, TN
    MS-Off Ver
    Excel 2003/2007/2010/2016
    Posts
    432

    Arrow Re: Print labels for items received on receiving report

    OK,

    I think that this is a good start. Almost everything is automated and one click does the entire merge, but I need some help:

    1. On the RIR (Master Sheet), if the row is blank, you should not be able to put an X in column A to send that (blank) row to the Label sheet.

    2. The code that send the rows to the Labels sheet sends over some columns that I don't need for the labels. Only the light-blue headered columns are needed, but the code sends the entire range to the Labels sheet.

    3. Probably more for the Word forum, but:
    a) The code opens the Word template, does the Merge, creates a new sheet with the merged data, but then leaves the template open. The template should be closed after the new sheet is created.

    b) I see alot about Error 429 and multiple instances of Word, so maybe I need some error-checking for that. (It works fine now, but I don't want it to break because of that.)


    Here's the code for copying the X'ed rows to the Labels sheet:

    Please Login or Register  to view this content.

    Here's the code for the button on the Labels sheet for the merge process:

    Please Login or Register  to view this content.
    That's all for now. If anyone has a chance to look at this, I would appreciate the help!

    Respectfully,

    Lost
    Attached Files Attached Files
    Last edited by leaning; 06-23-2011 at 05:08 PM. Reason: typos

  8. #8
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Print labels for items received on receiving report

    Maybe this is what you need.

    Select the addresses in the listbox you want to be 'labeled out'
    Choose the first labelnumber to be printed in a A4 sheet.
    Click 'Save'
    Attached Files Attached Files
    Last edited by snb; 06-23-2011 at 05:17 PM.

  9. #9
    Forum Expert Mordred's Avatar
    Join Date
    07-06-2010
    Location
    Winnipeg, Canada
    MS-Off Ver
    2007, 2010
    Posts
    2,787

    Re: Print labels for items received on receiving report

    Hi snb, I've tried what you gave twice and both times it crashed excel. Plus, I was unable to exit the form (which was nicely laid out by the way). Mayhap it is because I didn't save and tried to run it from within the zip file.

  10. #10
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Print labels for items received on receiving report

    You have to unzip both files in the same directory first.

  11. #11
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    label tool

    In the attachment a 'revised revision'

    Unzip both files in the same directory: open the Excelfile.
    Attached Files Attached Files

  12. #12
    Valued Forum Contributor
    Join Date
    02-26-2010
    Location
    Chattanooga, TN
    MS-Off Ver
    Excel 2003/2007/2010/2016
    Posts
    432

    Question Re: Print labels for items received on receiving report

    All,

    This code was working, but now it's not.

    If there is an X (or anything) in cells A8:A19, then copy the row (or rows) (I really need to only copy Columns B:H and R:V) to the next available row on the sheet named "Labels".

    Right now, it only copies the first X'ed row and nothing after. Not looping. Anyone see what is wrong?

    Please Login or Register  to view this content.
    Lost

  13. #13
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Print labels for items received on receiving report

    Please Login or Register  to view this content.
    doesn't look right.

  14. #14
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Print labels for items received on receiving report

    You can't copy non-contiguous ranges.

  15. #15
    Valued Forum Contributor
    Join Date
    02-26-2010
    Location
    Chattanooga, TN
    MS-Off Ver
    Excel 2003/2007/2010/2016
    Posts
    432

    Re: Print labels for items received on receiving report

    Marvin P:

    I got the code here:

    http://www.ozgrid.com/forum/showthre...t=52646&page=1

    It just copies one row, then stops.

    ?

    Lost

  16. #16
    Valued Forum Contributor
    Join Date
    02-26-2010
    Location
    Chattanooga, TN
    MS-Off Ver
    Excel 2003/2007/2010/2016
    Posts
    432

    Re: Print labels for items received on receiving report

    Hello!

    "If there is an X (or anything) in cells A8:A19 on Sheet A, then copy the row (or rows) to the next available row on Sheet B."

    I find plenty of code to copy the formulas, but I want to copy just the values. The copied-over IF formulas are giving #REF errors.

    I'll keep Googling, but if anyone has this code handy, I would appreciate it.

    VR/Lost


    (This post relates to the workbook on this thread, plus I already had this same problem earlier in the thread, so I didn't want to double-post to a new thread.)

  17. #17
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Print labels for items received on receiving report

    Please open the attachment in this post.
    You will find the method you are asking for (and much more).

  18. #18
    Valued Forum Contributor
    Join Date
    02-26-2010
    Location
    Chattanooga, TN
    MS-Off Ver
    Excel 2003/2007/2010/2016
    Posts
    432

    Question Re: Print labels for items received on receiving report

    All,

    How this works: Enter data in the columns on the first sheet, put an X in the A column, and hit a button to copy those selected rows over to the second sheet. From the second sheet, you can edit the values, then hit a button to put those values onto an Avery label.

    Here's the question:

    For any value of "H" (for on-hold) in the H column, when that row is copied to the Labels sheet, it needs to make a copy of the row, but with the H turned into an A and the Status (column M) turned into "ITEM ON HOLD".

    (Select A,N,C, or NS, one tag prints. If H is selected, an H and an A tag need to print with the same information on them. But if the H is the tenth tag (not necessarily tag #10, then it won't copy it because H + A will exceed the 10 tag limit.)

    Hope I explained that good enough. Workbook and Label template attached and should go into the same folder to work.

    I appreciate the help!

    Lost
    Attached Files Attached Files

  19. #19
    Valued Forum Contributor
    Join Date
    02-26-2010
    Location
    Chattanooga, TN
    MS-Off Ver
    Excel 2003/2007/2010/2016
    Posts
    432

    Re: Print labels for items received on receiving report

    All,

    I am still working with this code:

    Please Login or Register  to view this content.
    How it should work:
    a) Copy a row.
    b) If it had an H in the H column, make a copy of the row just copied only with H turned into an A and the Status (column M value) turned into "ITEM ON HOLD".
    c) Keep copying rows, adding an A row for any H's.
    d) Stop copying if the row=12 (that will be ten labels for the Avery sheet).

    I don't know how to handle if the tenth label to be copied over has an H. The program would add an A row (H's always get an A), but then that would make 11 labels.

    I am wrapped around this, bigtime. Anyone have any idea how to make this work?



    Respectfully,

    Lost

  20. #20
    Forum Expert Mordred's Avatar
    Join Date
    07-06-2010
    Location
    Winnipeg, Canada
    MS-Off Ver
    2007, 2010
    Posts
    2,787

    Re: Print labels for items received on receiving report

    Quote Originally Posted by snb View Post
    Please open the attachment in this post.
    You will find the method you are asking for (and much more).
    Hi snb, I just unzipped and opened your workbook (with the word document also unzipped and in the same folder) and I wasn't even able to get past the warning in the beginning because the digital signature is invalid. Also, your Word document was attempting to connect to a .csv using an SQL statement. Any ideas?

  21. #21
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Print labels for items received on receiving report

    @Mordred

    Quote Originally Posted by Mordred
    and I wasn't even able to get past the warning in the beginning because the digital signature is invalid.
    You always get a warning when a developer has used selftcert.exe (part of MS Office).
    You can 'pass' by accepting the signature as safe (unless you do not trust me at all ). It means that in the future my solutions will run more smoothly on your computer.
    I'd say: start accepting my digital signature. After that I hope the rest will be self-evident.
    Last edited by snb; 08-30-2011 at 09:22 AM.

  22. #22
    Forum Expert Mordred's Avatar
    Join Date
    07-06-2010
    Location
    Winnipeg, Canada
    MS-Off Ver
    2007, 2010
    Posts
    2,787

    Re: Print labels for items received on receiving report

    It took me by surprise snb as I haven't had that occur from this site until yesterday. I'm going to try again when I get home from work tonight.

  23. #23
    Valued Forum Contributor
    Join Date
    02-26-2010
    Location
    Chattanooga, TN
    MS-Off Ver
    Excel 2003/2007/2010/2016
    Posts
    432

    Re: Print labels for items received on receiving report

    Mordred/snb,

    snb: I didn't mean to not acknowledge your post or disrespect you in any way. (Ignorance of the rules is no excuse.) I have your file and have been fiddling with it, but I can't see how I can apply it with what I am trying to do. (Your post has actually got more play (7 views) than the workbook I posted (2 views), so even if I omitted to say anything, it looks like others are seeing that it has value and is getting hits.)

    AFA my workbook, I posted on 8/24 the last problem I was having with this workbook before it can go live. I thought maybe that post was getting lost in the shuffle, so I reposted the specific issue to a different thread on 8/24. Without this last issue resolved, the workbook is unusable, and any work leading to this point is lost.

    I more than appreciate the help this forum has provided. I realize the help is free and am still waiting for the day when my seed rate overcomes my leach rate.

    Lost
    Last edited by leaning; 09-02-2011 at 08:31 AM. Reason: wording

  24. #24
    Valued Forum Contributor
    Join Date
    02-26-2010
    Location
    Chattanooga, TN
    MS-Off Ver
    Excel 2003/2007/2010/2016
    Posts
    432

    Re: Print labels for items received on receiving report

    Hello!

    Bumping to see if anyone has any ideas on how to fix this one last issue with this workbook (Copy a row to another sheet, but change a few fields in the copy.)

    Thanks!

    Lost

  25. #25
    Forum Expert 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: Print labels for items received on receiving report

    Hi Lost

    I must be missing something...this code changes Column H from an "H" to an "A" and places "ITEM ON HOLD" in Column "M". I don't understand this
    The program would add an A row (H's always get an A), but then that would make 11 labels.
    Please Login or Register  to view this content.
    Tell me what I'm missing.
    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.

  26. #26
    Valued Forum Contributor
    Join Date
    02-26-2010
    Location
    Chattanooga, TN
    MS-Off Ver
    Excel 2003/2007/2010/2016
    Posts
    432

    Re: Print labels for items received on receiving report

    Jaslake,

    Progress!

    1. Here's the best way I can describe it:

    a) Stuff comes in the mail.
    b) We write down everything about that item in the Inspection Report.
    c) Then we inspect it.
    d) If it is Acceptable, we slap an "Acceptable" tag on it (blue text when it does the mail merge).
    e) If the item has a defect, we slap an "NCR" tag on it (red text).
    f) If we don't have time to look at it, we slap a "Hold" tag and an "Acceptable" tag on it.

    So, for any item marked H, it should <also> print an A tag with that same information.

    2. This workbook and mail merge is set up for Avery 5163, so there are only 10 labels per sheet.

    3. One other thing I tried to fix with no luck is that the row copy copies over columns A-P (16 columns). I only need B-P copied over (I don't need the X's). On the label sheet, I was going to write 1-10 down the A-column, and then off to the side have 10 boxes (a mini-label sheet, but without the info), so the user can see where Label 5 (for example) is going to print.

    HTH

    Lost

  27. #27
    Valued Forum Contributor
    Join Date
    02-26-2010
    Location
    Chattanooga, TN
    MS-Off Ver
    Excel 2003/2007/2010/2016
    Posts
    432

    Talking Re: Print labels for items received on receiving report

    All,

    My other thread sums up how this code and information gets applied.

    http://www.excelforum.com/excel-prog...-the-copy.html

    HTH!

    Lost

+ 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