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
|