Before Print - one worksheet only

[jeffbert]

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

 

[Bob Phillips]

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]

Bob

This works beautifully. Thank you for also cleaning up the code.

Thanks again

Jeff