How to auto copy from file to file with out of order data?

Is there an easy way to automatically copy data from 1 Excel spreadsheet to another when the data is slightly out of order?

Currently an admin copies and pastes from file 1 to file 2 manually. There are approximately 800 names to copy. It would be ideal to type a command or press a key and have file 2 updated automatically. Is there a feasible way to accomplish this without spending a lot of money or time?


file 1 file 2

john 100 john 100
june 200 lance 50
marie 150 june 75
stan 100 marie 125
phone 50 stan 200
hold 25

In the example above, if lance doesn't appear in file 1 his amount doesn't get copied and his 50 remains the same. The amounts do not get added, just pasted over from file 1 to file 2 with the days sales results. Only the names on file 2 get pasted over. Phone and hold amounts in file 1 are ignored.

Any help would be appreciated.

Hello John444,

Welcome to the Forum!

The following macro can be attached to button from the Forms Toolbar on the worksheet. There are a few variable in the code you should change to what you need. These are marked in red. Make these changes before you save the macro.

The macro assumes the information starts in the same cell on each sheet, for example "A2". It also assumes that the name is in the first column "A" and the amount is in the adjacent column "B".

Please Login or Register  to view this content.

Installing the Macro

1. Copy the macro using the CTRL+C keys.
2. Open the Excel workbook the macro will run in.
3. Open the Visual Basic Editor (VBE) using the keys ALT+F11.
4. Activate the Insert Menu with the keys ALT+I.
5. Insert a new module for the macro by pressing the key M.
6. Paste the macro into the module using the keys CTRL+V.
7. Make any custom changes to the code at this time!
8. Save the macro using the keys CTRL+S.
9. Exit the VBE and return to Excel using ALT+Q.

Sincerely,
Leith Ross

Thanks! However, unfortunately the data isn't in the same cells on both files. The emp names are the same. The file being copied to contains formulas which can't be overwritten. Is it still possible to write a macro to accomplish the automatic copying?

Hello John444,

Most likely the copying can be automated. You would need to post your workbook for review before I can give you an answer.

Sincerely,
Leith Ross