How use macro to print based on cell content.

February 2, 2011 at 23:28:19
Specs: Windows XP
I use a macro to print shipping forms & labels based on data entered on a "Data Entry" worksheet. When someone pushes the button the macro transfer this data from the data entry page to another worksheet (used as a database/history), prints the forms/labels to specific printers, clears the fields on the "Data Entry" page, and saves the file.

A couple of the labels are only required if data is entered into specific fields, so we are currently wasting labels using the current macro.

The workbook has the following worksheets: Data Entry, Std Cert Form, Label, ILT, Line #

The ILT worksheet only needs to print if there is data in B16 on the Data Entry worksheet

The Line # worksheet only needs to print if there is data in B18 on the Data Entry worksheet

Here is the portion of the macro that currently deals with the printing:

Sheets("Label").Select
Application.ActivePrinter = "DYMO LabelWriter 450 Turbo (Copy 1) on Ne00:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"DYMO LabelWriter 450 Turbo (Copy 1) on Ne00", Collate:=True
Sheets("ILT").Select
Application.ActivePrinter = "DYMO LabelWriter 450 Turbo (Copy 1) on Ne00:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"DYMO LabelWriter 450 Turbo (Copy 1) on Ne00", Collate:=True
Sheets("LINE #").Select
Application.ActivePrinter = "DYMO LabelWriter 450 Turbo (Copy 1) on Ne00:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"DYMO LabelWriter 450 Turbo (Copy 1) on Ne00", Collate:=True
Sheets("Std Cert Form").Select
Application.ActivePrinter = "\\exprint1\EX HCISHIP on Ne04:"
ActiveWindow.SelectedSheets.PrintOut Copies:=2, ActivePrinter:= _
"\\exprint1\EX HCISHIP on Ne04:", Collate:=False

How can I modify this code to reduce waste? Any help would be appreciated.

Thank you in advance.


See More: How use macro to print based on cell content.

Report •

#1
February 2, 2011 at 23:33:47
Sorry the macro code did not stay properly indented when I posted the message.

Each line that starts with " should be indented.


Report •

#2
February 3, 2011 at 04:50:38
Please read the How To referenced in my signature line and repost your code.

The indents will hold if you use the pre tags.

  Line 1
    Line 2
      Line 3

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#3
February 3, 2011 at 05:07:29
Thank you for the help and the information on lining up the code properly. Looks good in the preview pane now. Please see the corrected code below.

Sheets("Label").Select
Application.ActivePrinter = "DYMO LabelWriter 450 Turbo (Copy 1) on Ne00:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
    "DYMO LabelWriter 450 Turbo (Copy 1) on Ne00", Collate:=True
Sheets("ILT").Select
Application.ActivePrinter = "DYMO LabelWriter 450 Turbo (Copy 1) on Ne00:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
    "DYMO LabelWriter 450 Turbo (Copy 1) on Ne00", Collate:=True
Sheets("LINE #").Select
Application.ActivePrinter = "DYMO LabelWriter 450 Turbo (Copy 1) on Ne00:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
    "DYMO LabelWriter 450 Turbo (Copy 1) on Ne00", Collate:=True
Sheets("Std Cert Form").Select
Application.ActivePrinter = "\\exprint1\EX HCISHIP on Ne04:"
ActiveWindow.SelectedSheets.PrintOut Copies:=2, ActivePrinter:= _
    "\\exprint1\EX HCISHIP on Ne04:", Collate:=False


Report •

Related Solutions

#4
February 3, 2011 at 12:05:26
Obviously I can't test your code because I don't have your printers or workbooks, but I have a few suggestions.

It seems to me that you have a lot of redundancy in your code.

1 - You are setting the ActivePrinter to the DYMO printer before each .PrintOut instruction and then including the printer name once again within the .PrintOut line.

Set it once, do your PrintOuts, and then set it back to the HCISHIP printer

2 - Rarely do you have to Select an object within VBA to perform an operation on it.

This:

  Sheets("Label").Select
   ActiveWindow.SelectedSheets.PrintOut 

can usually be reduced to this:

  
Sheets("Label").PrintOut 

As I said, I can't test it, but you can.

If all that I've said is true and applies to your workbook, then the entire section of code might look like the following. Even if it all doesn't work, I think the answer to your question about only printing certain sheets when specific cells have values in them should be evident.

 Application.ActivePrinter = "DYMO LabelWriter 450 Turbo (Copy 1) on Ne00:"
   Sheets("Label").PrintOut
      If Sheets("Data Entry").Range("B16") <> "" Then Sheets("ILT").PrintOut
      If Sheets("Data Entry").Range("B18") <> "" Then Sheets("LINE #").PrintOut
  Application.ActivePrinter = "\\exprint1\EX HCISHIP on Ne04:"
   Sheets("Std Cert Form").PrintOut Copies:=2

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#5
February 3, 2011 at 23:45:49
Thank you very much for the assistance. The code worked quite nicely with only a minor change to printer addressing.

I am pretty new to working with macros, but am learning as I go. Most of this macro was done via recording prior to me, so I am sure there is redundancy throughout and as I continue to learn (via trial & error or assistance from people such as yourself) I will revisit it.

Thanks to your assistance the most important aspect of this code is now true - it works!


Report •

Ask Question