Results 1 to 6 of 6

Thread: [RESOLVED] Big Help on a Printing Macro

  1. #1

    Thread Starter
    New Member
    Join Date
    Feb 2021
    Posts
    4

    Resolved [RESOLVED] Big Help on a Printing Macro

    Long story short, I inherited this pallet tag printing excel booklet. One of our new managers brought it from a previous business and I'm the only advanced Excel user in the building, though I have little to no VBA experience. I know enough general basics to have it mostly doing what they want, but it looks like it was created around 2012 and hasn't been updated since 2013. It has a few modules/macros doing record keeping stuff that we won't use. We just want to use the tag printing option, so I've ignored it. To use, you input the order into columns B, C, D, F, and J on the Data tab, click in column A so it fills (not sure why they have this happening this way). The number of pallets and case remainders in columns G & H and the Tag color auto fills and you hit "Print Date" to generate a Pallet tag for each Pallet and a Pallet for remainders. It prints the remainders first, then each of the full pallet tags. The problem I'm running into is, it stops printing full pallet tags after any item in the order that has 2 or more pallets. In the attached file, line 3 of the order has 2 pallets, so no pallet tag is printed for lines 4 and 5. Though the case remainders print fine for every line. It doesn't matter what line the 2 or more pallets falls in, it simply won't print full pallet tags after that line. I'm just not experienced enough to understand why it's hanging up. We're getting around it by making multiple lines for products with more than 2 pallets. If anyone wants to take a look at this and help, it would be greatly appreciated. I've changed my products and customer listing for obvious reasons.
    Attached Files Attached Files

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Big Help on a Printing Macro

    i have not yet found the issue, but i would recommend that all unqualified ranges (which would refer to sheet data) should be fully qualified to a sheet object
    Code:
    set shdata = sheets("data")
    or possibly even better, to use a with block


    the second thing i notice is, assuming that C2 on the label sheet is the pallet number, that it is not incremented for each pallet

    i will look some more later
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3

    Thread Starter
    New Member
    Join Date
    Feb 2021
    Posts
    4

    Re: Big Help on a Printing Macro

    C2 on the "Label" is the Bill Of Lading (BOL) number, statically entered on the "data" tab by the user. It should not change per pallet. It's the over all "order" number connecting all of the pallets on that truck. I'm inexperienced enough that I am not totally sure what you mean by "fully qualified boject". I THINK you mean referring to a specific object or range as opposed to just referencing the whole "data" sheet? I apologize for my lack of knowledge and experience.

  4. #4
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Big Help on a Printing Macro

    I THINK you mean referring to a specific object or range as opposed to just referencing the whole "data" sheet?
    not quite, in this macro all ranges on label sheet are fully qualified, sheet object.range, but ranges on data sheet are not qualified so just refer to the current activesheet, this is sort of unsafe /bad practice and should be avoided, even if it has never caused a problem in the past

    i tested your code and it appeared to work as required, printed all pallets from the list
    note: i do not print out to an actual printer, just a virtual one
    the problem may have to do with a timing issue when the code runs too fast for the printer to accept more printouts, have you tested stepping through the code to see if it works that way, or tried a different printer

    i am not sure why the code was written the way it is, i would have thought it would have been more logical to print all the pallets and partial for each line in turn, though there may be some reason why they wanted all the partials printed first as is the case here, possibly to fill the partial pallets first, and i have no idea why the partial printout is at the beginning of the loop instead of at the end of the loop

    one thing to try, would be to remove the loop to print out the number pallets and just specify the number of pages to print
    Code:
            For K = 1 To Range("H" & I).Value
               Sheets("Label").PrintOut
            Next K
    ' change to
               Sheets("Label").PrintOut ,, Range("H" & I).Value
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  5. #5

    Thread Starter
    New Member
    Join Date
    Feb 2021
    Posts
    4

    Re: Big Help on a Printing Macro

    I'll try a static page count, thank you! I know this thing is messy, it was written nearly a decade ago and not by me. I appreciate the help and patience! I hadn't thought of the printer being to slow for the macro. We're all on network printers, so I'll try printing it virtually to see if it works out for me, like it did you, as well. I'll tag this thread solved if it works!

    Thanks!

  6. #6

    Thread Starter
    New Member
    Join Date
    Feb 2021
    Posts
    4

    Re: Big Help on a Printing Macro

    Quote Originally Posted by westconn1 View Post

    one thing to try, would be to remove the loop to print out the number pallets and just specify the number of pages to print
    Code:
            For K = 1 To Range("H" & I).Value
               Sheets("Label").PrintOut
            Next K
    ' change to
               Sheets("Label").PrintOut ,, Range("H" & I).Value

    Worked perfectly! I cannot thank you enough!

Posting Permissions

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



Click Here to Expand Forum to Full Width