workbook before print event

[realniceguy5000]

Hi I have a script I added to the workbook before print event that copies sheet(1) removes the colors and changes a few things on the worksheet. then prints the current sheet. then deletes the sheet. the problem I am having is since this is triggered when the print button is press on sheet(1)it also prints out the orginal sheet .


How can I stop the script from printing the orginal sheet?


Here is the script I am using.

Thank You, Mike

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim cell As Object
Dim r As Range
Dim lrow As Long
Sheets("Summary").Copy Before:=Sheets(1)
Cells.Interior.ColorIndex = xlNone: Cells.Font.ColorIndex = 0
lrow = Range("Q65000").End(xlUp).Row - 5
Set r = Range("Q4:Q" & lrow)
For Each cell In r
If cell.Value < 225 Then
cell.Interior.ColorIndex = 3: cell.Font.ColorIndex = 2
End If
Next cell
Range("A3:Q3").Interior.ColorIndex = 2
Range("A" & lrow + 1, Range("Q" & lrow + 1)).Interior.ColorIndex = 2
Range("A" & lrow + 4, Range("Q" & lrow + 4)).Interior.ColorIndex = 2
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Application.DisplayAlerts = False
Sheets("Summary (2)").Delete
Range("A1:Q1").Select
End Sub

 

[VoG]

Try

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim cell As Object
Dim r As Range
Dim lrow As Long
Cancel = True
Sheets("Summary").Copy Before:=Sheets(1)
Cells.Interior.ColorIndex = xlNone: Cells.Font.ColorIndex = 0
lrow = Range("Q65000").End(xlUp).Row - 5
Set r = Range("Q4:Q" & lrow)
For Each cell In r
    If cell.Value < 225 Then
        cell.Interior.ColorIndex = 3: cell.Font.ColorIndex = 2
    End If
Next cell
Range("A3:Q3").Interior.ColorIndex = 2
Range("A" & lrow + 1, Range("Q" & lrow + 1)).Interior.ColorIndex = 2
Range("A" & lrow + 4, Range("Q" & lrow + 4)).Interior.ColorIndex = 2
Application.EnableEvents = False
ActiveSheet.PrintOut Copies:=1, Collate:=True
Application.EnableEvents = True
Application.DisplayAlerts = False
ActiveSheet.Delete
Application.DisplayAlerts = True
End Sub