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

bluann4880

New Member
Joined
Jan 7, 2014
Messages
1
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.
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Watch MrExcel Video

Forum statistics

Threads
1,129,553
Messages
5,636,989
Members
416,953
Latest member
broexc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top