Fix Needed for Code to Send Correct Data to Correct Sheets

Hello and a very happy new year to you all.

I have a workbook containing three sheets. It's purpose is to allow users to add or make changes to a list of items, display the current item information, and keep a record of previous item data recorded. The first sheet (Entry Page) is for adding items/making item changes, the second (Current List) is for displaying current item information, and the third sheet (Historical List) is for keeping record of all the item entries ever made. A copy of the workbook is attached.

I am currently trying to perfect the code I have so far (thanks to the amazing help on this forum). It is partially doing what I want it to, and partially not - I'll explain:

The workbook opens to the Entry Page. The first value to enter is the item Number - Cell A2.

Here's the way it should work:

If the number entered is a new number (a number that is not yet listed on the Current List), the user would continue adding the rest of the information, then hit the Submit button - and the information would be entered in numerical order into the Historical List, displayed on the Current List, and the data on the Entry Page cleared to make ready for any further additions/changes. If the item number entered is already in the Current List, all the current information (Cells B3 through B10) of that item number would be displayed for the user to view. If the user changed the information and hit the Submit button, the updated information would be added to the Historical List, and the Current List would then display the latest update. And the Entry Page would be blank and ready once more for the next entry.

Here's where I'm at with my code:

1. When I submit a new item number and it's information and click Submit, the entry page data gets cleared like it should, and it all gets added perfectly to the Current List, but not to the Historical List. I think the code should be set up so that the data from the Data Entry page would get added to the Historical List first, and the Current List would be set up to show only the most current information from there.

2. When I change a number already in the Current List and hit Submit, the entry page data gets cleared like it should, and the information gets added to the Current List; but on the Historical List, another copy of the previous entry for that item gets added instead. Not sure what's wrong with the code there.

3. I have a message box come up right after entering a current item number asking whether I want to edit the information - I'd like to get rid of it altogether, but I'm not sure how to do that without messing up the part of the code that works.

Any suggestions?

Why have two lists?

This code will be faster doing the actual copying

Please Login or Register  to view this content.

I'm not sure why you are using the worksheet event

Why have two lists?

This code will be faster doing the actual copying

Please Login or Register  to view this content.

I'm not sure why you are using the worksheet event

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste
To run the Excel VBA code:

Choose Tools | Macro | Macros
Select a macro in the list, and click the Run button

Or add a button to the sheet