Print button breaks code but Manual print doesnt - help needed

jason2

New Member
Joined
Apr 2, 2009
Messages
6
Hi,

I have an excel sheet which creates delivery notes. Every time a delivery note is printed, I want to track it in another sheet.

I have the following line of code linked to a button.
Code:
ActiveWindow.SelectedSheets.PrintOut copies:=5
The problem is that the below macro runs when the sheet is printed using a BeforePrint. The macro only runs if a manual print is done but it breaks if I use the button.

The below is the code Im trying to run. It breaks on the line in blue.

Code:
Private Sub Workbook_BeforePrint(Cancel As Boolean)
'Print only if Note filled in properly
If Range("B32").Value <> "" Or Range("Q2").Value <> "" Then
Else
Cancel = True
MsgBox ("Please fill in Orginator box and the Consignment Note number!")
GoTo End1
End If
If Range("B32").Value <> "" Then
Else
Cancel = True
MsgBox ("Please fill in the Orginator box!")
GoTo End1
End If
If Range("Q2").Value <> "" Then
Else
Cancel = True
MsgBox ("Please fill in the Consignment Note number!")
GoTo End1
End If
'Printed Notes
Application.ScreenUpdating = False
Set a = ActiveSheet
Workbooks.Open Filename:= _
    "C:\Documents and Settings\Jason.THESIMPSONS.001\Desktop\Printed Notes.xls"
j = 1
Workbooks("ASCO Consignment Note.xls").Activate
[COLOR=blue]Do Until IsEmpty(Workbooks("Printed Notes.xls").Sheets("Printed Notes").Range("A" & j)) 'Code Breaks Here.[/COLOR]
j = j + 1
Loop
'Tracking
Workbooks("Printed Notes.xls").Sheets("Printed Notes").Range("A" & j).Value = a.Range("G7").Value
Workbooks("Printed Notes.xls").Sheets("Printed Notes").Range("B" & j).Value = a.Range("R33:U33").Value
Workbooks("Printed Notes.xls").Sheets("Printed Notes").Range("C" & j).Value = a.Range("R34:U34").Value
Workbooks("Printed Notes.xls").Sheets("Printed Notes").Range("D" & j).Value = a.Range("R35:U35").Value
Workbooks("Printed Notes.xls").Sheets("Printed Notes").Range("E" & j).Value = a.Range("R36:U36").Value
Workbooks("Printed Notes.xls").Sheets("Printed Notes").Range("F" & j).Value = Now()
Workbooks("Printed Notes.xls").Sheets("Printed Notes").Range("G" & j).Value = a.Range("B32").Value
Workbooks("Printed Notes.xls").Sheets("Printed Notes").Range("H" & j).Value = a.Range("Q2").Value
Workbooks("Printed Notes.xls").Sheets("Printed Notes").Range("I" & j).Value = a.Range("G10").Value
Workbooks("Printed Notes.xls").Sheets("Printed Notes").Range("J" & j).Value = a.Range("G11").Value
Workbooks("Printed Notes.xls").Sheets("Printed Notes").Range("K" & j).Value = a.Range("G12").Value
Workbooks("Printed Notes.xls").Sheets("Printed Notes").Range("L" & j).Value = a.Range("L11").Value
Workbooks("Printed Notes.xls").Sheets("Printed Notes").Range("M" & j).Value = a.Range("L12").Value
Workbooks("Printed Notes.xls").Sheets("Printed Notes").Range("N" & j).Value = a.Range("Q11").Value
Workbooks("Printed Notes.xls").Sheets("Printed Notes").Range("O" & j).Value = a.Range("Q12").Value
'Description
Workbooks("Printed Notes.xls").Sheets("Printed Notes").Range("P" & j).Value = a.Range("G17").Value
Workbooks("Printed Notes.xls").Sheets("Printed Notes").Range("Q" & j).Value = a.Range("G18").Value
Workbooks("Printed Notes.xls").Sheets("Printed Notes").Range("R" & j).Value = a.Range("G19").Value
Workbooks("Printed Notes.xls").Sheets("Printed Notes").Range("S" & j).Value = a.Range("G20").Value
Workbooks("Printed Notes.xls").Sheets("Printed Notes").Range("T" & j).Value = a.Range("G21").Value
Workbooks("Printed Notes.xls").Sheets("Printed Notes").Range("U" & j).Value = a.Range("G22").Value
Workbooks("Printed Notes.xls").Sheets("Printed Notes").Range("V" & j).Value = a.Range("G23").Value
Workbooks("Printed Notes.xls").Sheets("Printed Notes").Range("W" & j).Value = a.Range("G24").Value
Workbooks("Printed Notes.xls").Sheets("Printed Notes").Range("X" & j).Value = a.Range("G25").Value
Workbooks("Printed Notes.xls").Sheets("Printed Notes").Range("Y" & j).Value = a.Range("G26").Value
Workbooks("Printed Notes.xls").Sheets("Printed Notes").Range("Z" & j).Value = a.Range("G27").Value
Workbooks("Printed Notes.xls").Sheets("Printed Notes").Range("AA" & j).Value = a.Range("G28").Value
'Save
Workbooks("Printed Notes.xls").Save
Workbooks("Printed Notes.xls").Close
End1:
Application.ScreenUpdating = True
End Sub
Any help will be appreciated greatly as it will save a lot of time at work.

Thanks
 

Forum statistics

Threads
1,081,556
Messages
5,359,555
Members
400,533
Latest member
fpenning

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top