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

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

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