VBA to move specific cell to a table in another worksheet

[markharris2004]

I have a workbook where sheet 1 is a form where fields need to be completed before allow the auto save and rename and email to multiple recipients. I now want to go a step further and move these cells into a table in sheet 2 where it looks up and aligns all the data against the number in cell M11.
Basically it is a form on sheet 1 and then a record of that data in sheet 2. I will then clear out the form ready to start with the next one.

I will manually populate Column A on sheet 2 with the numbers

Attached is the file hopefully someone can help unless I have confused all of you

many thanks

[judgeh59]

I changed your IF Statement format a bit, I hope you don't mind...I think it works the way you asked...I put a hook in there that if the data isn't there it deletes that row of data so there will be a fresh row of data....hope that make sense....

[judgeh59]

did this help?

[markharris2004]

Many thanks, works perfectly. I have made some changes to the text fields added. I just need a liitle more help.

Stages A & B are completed straight away so only these fields need to be checked that not blank and copied across. It could be a few weeks before stage C is closed off. Is there a way of looking at the number at the top and then overwriting on that row of data rather than creating a new line. I then will sort sheet 2 by numerical order so the next person requesting picks the next available number.

Also is there a way then to copy sheet 1 to a new workbook, save and rename which I can then auto email? Am I being cheeky now?

Attached is the revised code and thank you again

[judgeh59]

yes, you are being a bit cheeky....fortunately I love British Humour.....(The IT Crowd was one of my favorites).....I created a loop that looks for the number and if it doesn't find it it uses last row....if it finds it, it overwrites to data...there is a way to xfer data to a new workbook and save it....unfortunately I just got handed a project that I have get done....you can search in this forum or else where on how to do

[markharris2004]

Awesome - Thank You

[judgeh59]

no prob....glad I could help and thanks for the points....