Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post

    Printing from a macro (Excel XP)

    Hi all,

    I have a macro that does a number of steps, including printing a small range of cells to a label printer. I'm just setting this up and have tested it on my UPS label printer and the macro works fine. I have a new Dymo label printer coming in the next couple days and will adjust the macro to print the labels to that instead.

    Currently the macro prints the range of cells to one label. I would like to make it repeat the same range of cells on multiple labels....ie, several indetical labels. The quantity would change depending upon the number of labels needed. Sometimes it would be one, and other times it would be some quantity greater then one.

    I have a cell on the spreadsheet that always specifies the number of labels needed. What I would like to set up is for the macro to look at that cell and print the matching quantity of labels for the number in that cell.

    As always thanks for taking the time to give me a hand.

    BH Davis

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Printing from a macro (Excel XP)

    You can use something like this:

    Range("A1:B4").PrintOut Copies:=Range("F1")

    Cells A1:B4 are printed, and the number of copies to be printed is taken from cell F1.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Printing from a macro (Excel XP)

    Hans,

    Simple, sweet and exactly what I needed. Thanks for saving me about an hour looking through my Excel handbook!

    BH

  4. #4
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Printing from a macro (Excel XP)

    Hans,

    Well, it's sort of working, but some weird printing is taking place.

    Look for the following line about 1/4 of the way down the attached .txt file of the macro:

    ' The following prints the 2 information labels to the UPS printer

    The area in question is just below that.

    The results of adding the line you suggested are that one copy of the selected range prints to the label printer, but whatever the qty was in the C12 cell then prints to my default system printer. So, if I specified a quantity of "3", one label printed on the label printer and 3 labels printed on the default system printer.

    Thanks for taking a look.
    BH

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Printing from a macro (Excel XP)

    The line

    Range("AF64:AI94").PrintOut Copies:=Range("C12")

    occurs before you have ever set the printer. You should either move the line

    Application.ActivePrinter = "UPS Thermal 2442 on Ne02:"

    up to before the PrintOut line, or specify the printer in the instruction itself:

    Range("AF64:AI94").PrintOut Copies:=Range("C12"), ActivePrinter:="UPS Thermal 2442 on Ne02:"

    or

    ActiveWindow.SelectedSheets.PrintOut Copies:=Range("C12"), _
    ActivePrinter:="UPS Thermal 2442 on Ne02:", Collate:=True

    Note: it isn't necessary to set Application.ActivePrinter *and* specify the printer in the PrintOut instruction. Neither should you have two instructions to print out the same range.

  6. #6
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Printing from a macro (Excel XP)

    Hans,

    Thank you! That did the trick.

    As to the double call to the printer that is the way it gets set up if I use the RECORD NEW MACRO feature. To be honest I use that to create my basic macros and then modify them as needed over time. For some reason the listing of the range first and the double stating of the printer is how the recorder sets it up. Probably something I'm doing in the steps during the recording proceedure that didn't effect it when I only wanted one copy, but obviously does effect things when I now want two!

    Anyway, thanks again for your willingness to help others!

    BH Davis

Posting Permissions

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