Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Before Print - one worksheet only
The following code is courtesy of Norman Jones, with some modifications to
check both column A and B. It works extremely well. However, I only need to apply the code to one worksheet in the workbook, not all. Is there a way that I can have it check to see if the active worskheet = "New Item Info", and if it does, execute, if not, do not run? Thanks Jeff Private Sub Workbook_BeforePrint(Cancel As Boolean) Dim ans As Long ans = MsgBox("Print Area will be adjusted to the last row in which there is a UPC or SKU #.", vbOKCancel) If ans = vbOK Then With ActiveSheet On Error Resume Next Dim SH As Worksheet Dim rng As Range Dim LRow As Long Dim LRow2 As Long Set SH = ActiveSheet With SH LRow = SH.Cells(Rows.Count, "A").End(xlUp).Row + 1 LRow2 = SH.Cells(Rows.Count, "B").End(xlUp).Row + 1 If LRow = LRow2 Then Set rng = .Range("A4:AN" & LRow) .PageSetup.PrintArea = rng.Address ElseIf LRow2 LRow1 Then Set rng = .Range("A4:AN" & LRow2) .PageSetup.PrintArea = rng.Address End If End With End With ElseIf ans = vbCancel Then End If End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Before Print - one worksheet only
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim ans As Long If ActiveSheet.Name = "New Item Info" Then ans = MsgBox("Print Area will be adjusted to the last " & _ "row in which there is a UPC or SKU #.", vbOKCancel) If ans = vbOK Then With ActiveSheet On Error Resume Next Dim SH As Worksheet Dim rng As Range Dim LRow As Long Dim LRow2 As Long Set SH = ActiveSheet With SH LRow = SH.Cells(Rows.Count, "A").End(xlUp).Row + 1 LRow2 = SH.Cells(Rows.Count, "B").End(xlUp).Row + 1 If LRow = LRow2 Then Set rng = .Range("A4:AN" & LRow) .PageSetup.PrintArea = rng.Address ElseIf LRow2 LRow1 Then Set rng = .Range("A4:AN" & LRow2) .PageSetup.PrintArea = rng.Address End If End With End With ElseIf ans = vbCancel Then End If End If End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "jeffbert" wrote in message ... The following code is courtesy of Norman Jones, with some modifications to check both column A and B. It works extremely well. However, I only need to apply the code to one worksheet in the workbook, not all. Is there a way that I can have it check to see if the active worskheet = "New Item Info", and if it does, execute, if not, do not run? Thanks Jeff Private Sub Workbook_BeforePrint(Cancel As Boolean) Dim ans As Long ans = MsgBox("Print Area will be adjusted to the last row in which there is a UPC or SKU #.", vbOKCancel) If ans = vbOK Then With ActiveSheet On Error Resume Next Dim SH As Worksheet Dim rng As Range Dim LRow As Long Dim LRow2 As Long Set SH = ActiveSheet With SH LRow = SH.Cells(Rows.Count, "A").End(xlUp).Row + 1 LRow2 = SH.Cells(Rows.Count, "B").End(xlUp).Row + 1 If LRow = LRow2 Then Set rng = .Range("A4:AN" & LRow) .PageSetup.PrintArea = rng.Address ElseIf LRow2 LRow1 Then Set rng = .Range("A4:AN" & LRow2) .PageSetup.PrintArea = rng.Address End If End With End With ElseIf ans = vbCancel Then End If End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Before Print - one worksheet only
Bob
This works beautifully. Thank you for also cleaning up the code. Thanks again Jeff "Bob Phillips" wrote: Private Sub Workbook_BeforePrint(Cancel As Boolean) Dim ans As Long If ActiveSheet.Name = "New Item Info" Then ans = MsgBox("Print Area will be adjusted to the last " & _ "row in which there is a UPC or SKU #.", vbOKCancel) If ans = vbOK Then With ActiveSheet On Error Resume Next Dim SH As Worksheet Dim rng As Range Dim LRow As Long Dim LRow2 As Long Set SH = ActiveSheet With SH LRow = SH.Cells(Rows.Count, "A").End(xlUp).Row + 1 LRow2 = SH.Cells(Rows.Count, "B").End(xlUp).Row + 1 If LRow = LRow2 Then Set rng = .Range("A4:AN" & LRow) .PageSetup.PrintArea = rng.Address ElseIf LRow2 LRow1 Then Set rng = .Range("A4:AN" & LRow2) .PageSetup.PrintArea = rng.Address End If End With End With ElseIf ans = vbCancel Then End If End If End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "jeffbert" wrote in message ... The following code is courtesy of Norman Jones, with some modifications to check both column A and B. It works extremely well. However, I only need to apply the code to one worksheet in the workbook, not all. Is there a way that I can have it check to see if the active worskheet = "New Item Info", and if it does, execute, if not, do not run? Thanks Jeff Private Sub Workbook_BeforePrint(Cancel As Boolean) Dim ans As Long ans = MsgBox("Print Area will be adjusted to the last row in which there is a UPC or SKU #.", vbOKCancel) If ans = vbOK Then With ActiveSheet On Error Resume Next Dim SH As Worksheet Dim rng As Range Dim LRow As Long Dim LRow2 As Long Set SH = ActiveSheet With SH LRow = SH.Cells(Rows.Count, "A").End(xlUp).Row + 1 LRow2 = SH.Cells(Rows.Count, "B").End(xlUp).Row + 1 If LRow = LRow2 Then Set rng = .Range("A4:AN" & LRow) .PageSetup.PrintArea = rng.Address ElseIf LRow2 LRow1 Then Set rng = .Range("A4:AN" & LRow2) .PageSetup.PrintArea = rng.Address End If End With End With ElseIf ans = vbCancel Then End If End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Chart doesn't print w/worksheet properly but will print by itself | Excel Discussion (Misc queries) | |||
how do u print back2back print of different worksheet in exel. | Excel Worksheet Functions | |||
print a specific area within a worksheet by clicking on print? | Excel Worksheet Functions | |||
Multiple print areas, one worksheet -need to print to PDF! | Excel Programming | |||
Print Macro that will Print Entire worksheet except.... | Excel Programming |