Enter data, copy formulas, copy values, paste data, delete data

Hi All ExcelForum Test.xlsm

I am creating a spread sheet that is to be updated daily. I will have a lead sheet "test sheet' that I enter the data on in column B from cell A9 downwards. Once all of the data is entered I would like it to do the following:

• Copy down the formulas in L9:P9 to the bottom of entered data in column B starting in E9:I9
• Copy those values from E9-I9 down into the "data" worksheet directly underneath the last data entered
• Go back to the "Test Sheet" and delete all entered data in Column B and all of the copied formulas
• Have the macro ready to start again for the next day

Any help with this would be much appreciated.

Thank you!

I wasn't quite sure on some of your points. How is this?:

Please Login or Register  to view this content.

Hey mate

Thanks for the reply. That is kind of what i'm looking for except for the following:

I need the formulas from L9:P9 to be pasted in the EFGHI columns

After those formulas have been pasted i need to copy that data and paste values in the "data" worksheet directly under the last data entered (so it makes a huge list of data over time).

Please let me know if none of this makes any sense?

Thanks!

Any ideas?

Any help would be much appreciated!

Thanks!

Alf,
Adjusted code.
Please note on sheet data you have some gaps, so you might think the next empty row may between the gaps, but it is not. It is the last data you have.