Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
print from macro, using if formula
I currently use a macro to print several sheets from one workbook. I would
like the option to print some of the sheets with the condition of the value of a cell in the first sheet being 0. My current Macro looks like this, ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _ "EPSON Stylus C88 Series on Ne03:", Collate:=True I've been trying to use the following, ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _ "EPSON Stylus C88 Series on Ne03:", Collate:=IF(Title!D90,"True") and ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _ "EPSON Stylus C88 Series on Ne03:", Collate:=(IF(Title!D90,"True")) Any help is greatly appreciated. -- hog1 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
print from macro, using if formula
You might try either of these and see what you think:
====== Dim CollVar as Boolean If Thisworkbook.Worksheets("Title").Range("D9").Value 0 then CollVar = True else CollVar = False End if ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _ "EPSON Stylus C88 Series on Ne03:", Collate:=CollVar Or=== If Thisworkbook.Worksheets("Title").Range("D9").Value 0 then ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _ "EPSON Stylus C88 Series on Ne03:", Collate:=True Else ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _ "EPSON Stylus C88 Series on Ne03:", Collate:=False End if ===== Hope that helps. -- Mike Lee McKinney,TX USA "Hog1" wrote: I currently use a macro to print several sheets from one workbook. I would like the option to print some of the sheets with the condition of the value of a cell in the first sheet being 0. My current Macro looks like this, ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _ "EPSON Stylus C88 Series on Ne03:", Collate:=True I've been trying to use the following, ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _ "EPSON Stylus C88 Series on Ne03:", Collate:=IF(Title!D90,"True") and ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _ "EPSON Stylus C88 Series on Ne03:", Collate:=(IF(Title!D90,"True")) Any help is greatly appreciated. -- hog1 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
print from macro, using if formula
I have tried both of your suggestions and with both, the print will be
performed even when the value is 0. -- hog1 "mikelee101" wrote: You might try either of these and see what you think: ====== Dim CollVar as Boolean If Thisworkbook.Worksheets("Title").Range("D9").Value 0 then CollVar = True else CollVar = False End if ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _ "EPSON Stylus C88 Series on Ne03:", Collate:=CollVar Or=== If Thisworkbook.Worksheets("Title").Range("D9").Value 0 then ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _ "EPSON Stylus C88 Series on Ne03:", Collate:=True Else ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _ "EPSON Stylus C88 Series on Ne03:", Collate:=False End if ===== Hope that helps. -- Mike Lee McKinney,TX USA "Hog1" wrote: I currently use a macro to print several sheets from one workbook. I would like the option to print some of the sheets with the condition of the value of a cell in the first sheet being 0. My current Macro looks like this, ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _ "EPSON Stylus C88 Series on Ne03:", Collate:=True I've been trying to use the following, ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _ "EPSON Stylus C88 Series on Ne03:", Collate:=IF(Title!D90,"True") and ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _ "EPSON Stylus C88 Series on Ne03:", Collate:=(IF(Title!D90,"True")) Any help is greatly appreciated. -- hog1 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
print from macro, using if formula
Ahhh...I may have misunderstood what you were trying to accomplish. I
thought that you only wanted to turn off the collating feature if the value was zero. If you want to skip the entire print operation, you could use something like this: ====== If Thisworkbook.Worksheets("Title").Range("D9").Value = 0 Then GoTo SkipPrinting ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _ "EPSON Stylus C88 Series on Ne03:", Collate:=True SkipPrinting: ====== That assumes that there is more to the macro than just the print statement. If not, you can just insert this before the print statement: ==== If Thisworkbook.Worksheets("Title").Range("D9").Value = 0 Then Exit Sub ==== Lastly, if you wanted some sort of notification (so that the macro doesn't just end in the background) you could do something like: === If Thisworkbook.Worksheets("Title").Range("D9").Value = 0 Then Msgbox "Printing has been canceled since Title!D9=0" Exit Sub Else ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _ "EPSON Stylus C88 Series on Ne03:", Collate:=True End If === Hope that helps. -- Mike Lee McKinney,TX USA "Hog1" wrote: I have tried both of your suggestions and with both, the print will be performed even when the value is 0. -- hog1 "mikelee101" wrote: You might try either of these and see what you think: ====== Dim CollVar as Boolean If Thisworkbook.Worksheets("Title").Range("D9").Value 0 then CollVar = True else CollVar = False End if ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _ "EPSON Stylus C88 Series on Ne03:", Collate:=CollVar Or=== If Thisworkbook.Worksheets("Title").Range("D9").Value 0 then ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _ "EPSON Stylus C88 Series on Ne03:", Collate:=True Else ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _ "EPSON Stylus C88 Series on Ne03:", Collate:=False End if ===== Hope that helps. -- Mike Lee McKinney,TX USA "Hog1" wrote: I currently use a macro to print several sheets from one workbook. I would like the option to print some of the sheets with the condition of the value of a cell in the first sheet being 0. My current Macro looks like this, ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _ "EPSON Stylus C88 Series on Ne03:", Collate:=True I've been trying to use the following, ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _ "EPSON Stylus C88 Series on Ne03:", Collate:=IF(Title!D90,"True") and ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _ "EPSON Stylus C88 Series on Ne03:", Collate:=(IF(Title!D90,"True")) Any help is greatly appreciated. -- hog1 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
print from macro, using if formula
Thank You MikeLee!
Your help is greatly appreciated. I will be using this macro and variations of it on dozens of workbooks which I use daily. I've used a combination of your suggestions. If your interested the following is my complete macro. ' Macro4 Macro ' Macro recorded 2/2/2007 by Bob Dubray ' ' Keyboard Shortcut: Ctrl+Shift+A ' Application.Goto Reference:="Paint" Application.ActivePrinter = "hp deskjet 990c series on Ne01:" ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _ "hp deskjet 990c series on Ne01:", Collate:=True Application.Goto Reference:="Yard" ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Application.Goto Reference:="Machine" Application.Goto Reference:="RollCutter" ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Application.Goto Reference:="Bandsaw" Application.Goto Reference:="Ironworker" ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Application.Goto Reference:="Prep" ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Application.Goto Reference:="Plasma" ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Application.Goto Reference:="WeldingBay" ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Application.Goto Reference:="Shear" Application.Goto Reference:="Purchasing" ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Application.Goto Reference:="Cover" ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Application.Goto Reference:="Drawing" ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Application.Goto Reference:="DrawingHD4" ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Application.Goto Reference:="DrawingHD5" ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Application.Goto Reference:="Title" ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Application.Goto Reference:="Labels5HD" If ThisWorkbook.Worksheets("Title").Range("D13").Valu e = 0 Then MsgBox "Printing has been canceled since Title!D13=0" SkipPrint: Else ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _ "EPSON Stylus C88 Series on Ne03:", Collate:=True End If Application.Goto Reference:="Labels4HD" If ThisWorkbook.Worksheets("Title").Range("D11").Valu e = 0 Then MsgBox "Printing has been canceled since Title!D11=0" SkipPrintOut: Else ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _ "EPSON Stylus C88 Series on Ne03:", Collate:=True End If Application.Goto Reference:="LabelsCorral" If ThisWorkbook.Worksheets("Title").Range("D9").Value = 0 Then MsgBox "Printing has been canceled since Title!D9=0" SkipPrints: Else ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _ "EPSON Stylus C88 Series on Ne03:", Collate:=True End If Application.ActivePrinter = "hp deskjet 990c series on Ne01:" Sheets("Recent").Select Rows("3:3").Select Selection.Insert Shift:=xlDown Sheets("Title").Select Range("C4:E4").Select Selection.Copy Sheets("Recent").Select Range("A3").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Title").Select Application.CutCopyMode = False Range("D9:E9").Select Selection.Copy Sheets("Recent").Select Range("C3").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Title").Select Application.CutCopyMode = False Range("D11:E11").Select Selection.Copy Sheets("Recent").Select Range("D3").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Title").Select Application.CutCopyMode = False Range("D13:E13").Select Selection.Copy Sheets("Recent").Select Range("E3").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Title").Select Application.CutCopyMode = False Range("I4").Select Selection.Copy Sheets("Recent").Select Range("F3").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Rows("3:3").Select Application.CutCopyMode = False With Selection.Font .Name = "Arial" .Size = 12 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With With Selection .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Range("A1").Select Sheets("Title").Select Range("A1").Select End Sub -- hog1 "mikelee101" wrote: Ahhh...I may have misunderstood what you were trying to accomplish. I thought that you only wanted to turn off the collating feature if the value was zero. If you want to skip the entire print operation, you could use something like this: ====== If Thisworkbook.Worksheets("Title").Range("D9").Value = 0 Then GoTo SkipPrinting ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _ "EPSON Stylus C88 Series on Ne03:", Collate:=True SkipPrinting: ====== That assumes that there is more to the macro than just the print statement. If not, you can just insert this before the print statement: ==== If Thisworkbook.Worksheets("Title").Range("D9").Value = 0 Then Exit Sub ==== Lastly, if you wanted some sort of notification (so that the macro doesn't just end in the background) you could do something like: === If Thisworkbook.Worksheets("Title").Range("D9").Value = 0 Then Msgbox "Printing has been canceled since Title!D9=0" Exit Sub Else ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _ "EPSON Stylus C88 Series on Ne03:", Collate:=True End If === Hope that helps. -- Mike Lee McKinney,TX USA "Hog1" wrote: I have tried both of your suggestions and with both, the print will be performed even when the value is 0. -- hog1 "mikelee101" wrote: You might try either of these and see what you think: ====== Dim CollVar as Boolean If Thisworkbook.Worksheets("Title").Range("D9").Value 0 then CollVar = True else CollVar = False End if ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _ "EPSON Stylus C88 Series on Ne03:", Collate:=CollVar Or=== If Thisworkbook.Worksheets("Title").Range("D9").Value 0 then ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _ "EPSON Stylus C88 Series on Ne03:", Collate:=True Else ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _ "EPSON Stylus C88 Series on Ne03:", Collate:=False End if ===== Hope that helps. -- Mike Lee McKinney,TX USA "Hog1" wrote: I currently use a macro to print several sheets from one workbook. I would like the option to print some of the sheets with the condition of the value of a cell in the first sheet being 0. My current Macro looks like this, ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _ "EPSON Stylus C88 Series on Ne03:", Collate:=True I've been trying to use the following, ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _ "EPSON Stylus C88 Series on Ne03:", Collate:=IF(Title!D90,"True") and ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _ "EPSON Stylus C88 Series on Ne03:", Collate:=(IF(Title!D90,"True")) Any help is greatly appreciated. -- hog1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
need a print macro to print only a certain number of pages | Excel Discussion (Misc queries) | |||
Pivot Table macro to set print area and print details of drill down data | Excel Discussion (Misc queries) | |||
Macro to open print window and set to print entire workbook | Excel Discussion (Misc queries) | |||
HOW DO I PRINT USING A MACRO? | Excel Discussion (Misc queries) | |||
Create a print macro that would automatically select print area? | Excel Worksheet Functions |