If data on one worksheet = value of data on second sheet...

[bluann4880]

Macro for Excel 2003

I'm trying to create code that looks to see if the invoice number (generated by a secondary macro and not part of the problem) has been used before. If the invoice number is already used, I want the data to save OVER the previous data (updating the saved invoice). If the invoice number is not already used, I want it to save it to the new invoice number. There are two Worksheets that this is coming from. I want the macro to look at sheet 1 and check the invoice number against the table in sheet 2 containing the data of multiple invoices. If the invoice number is brand new, it should be saved on that line. If it is used before it should save on the line it was on before. I hope this makes sense. Here's the code I'm working with...

Sub Save_Invoice()
'
' Save_Invoice Macro
' Save Current Invoice in "Stored Invoices.xls"
'


'
Windows("Custom Invoice.xls").Activate
If Range("Custom Invoice.xls:E4")= Value.[Windows ("Stored Invoices.xls:Range(E65536).Select Selection.End(x1Up).Select ActiveCell.Offset(1, 0).Select)]

Windows("Stored Invoices.xls").Activate
Windows("Custom Invoice.xls").Activate
Range("E4").Select
Application.Run "PERSONAL.XLS!SaveDate.SaveDate"

Windows("Custom Invoice.xls").Activate
Range("B8").Select
Application.Run "PERSONAL.XLS!CustomerNumber.CustomerNumber"

Windows("Custom Invoice.xls").Activate
Range("A16:A46").Select
Application.Run "PERSONAL.XLS!ProductNumberSelect.ProductNumberSelect"
Windows("Stored Invoices.xls").Activate

Range("D65536").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Select

ActiveSheet.Paste

Windows("Custom Invoice.xls").Activate
Range("F16:F46").Select
Application.Run "PERSONAL.XLS!QtySelect.QtySelect"
Windows("Stored Invoices.xls").Activate

Range("E65536").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Select


ActiveSheet.Paste
Windows("Stored Invoices.xls").Activate
ActiveWorkbook.Save
Windows("Custom Invoice.xls").Activate
Range("B8").ClearContents
Range("A16:A46").ClearContents
Range("F16:F46").ClearContents
Range("B8").Select
ActiveWorkbook.Save
Else
Windows("Custom Invoice.xls").Activate
MsgBox ("Needs New Invoice Number")
End If

End Sub

The rest of the macro runs correctly except for the "IF/Then statement", and the Then part of it is currently just popping up a message box rather than saving it in the correct place.