macro to input corresponding data into a spread sheet

Hi

I have a spread sheet with data in column D (reference) and column E (quantity).
These changes regularly.

I would like a macro that will populate the quantity column into a second spreadsheet that has a corresponding reference number. This has column G as reference and column H as quantity. Please see attached spreadsheet as it perhaps explain my problem better and it shows the expected results.

Could you please let me know how to achieve this with a macro?

Thanks
Hossein

Hi Kokosek

I do need a macro as the information keep changing all the time. i.e. new quantities are generated and then it will need to be populated into the 2nd spreadsheet. And once this is done then the contents of 1st table are deleted and ready for the next batch of data. This happens a few times a day.

Therefore I need a macro that will copy the quantity into the corresponding 2nd table.

Thanks.

Hello kvsrinivasamurthy

Thank you for your reply - this is great. Can I ask for a minor change in your code please? Sheet2 is in another spreadsheet, could you change your code to reflect this please?
Sheet1 is in Q:\prod\test1.xlsm location and sheet2 is in q:\sap\test2.xlsm location.

Sorry should have made this more clear.

Thanks.