Copyng today's data into a collated worksheet

[YoungMacro]

Hi, this has been asked a few different ways on the internet but none of the answers solves my problem.
I wish 1 colleague to be able to complete a "today's" table and then run a macro that copies that data to the master / collated table. Ideally I'd like it to then email another colleague a copy of the "today's" table. It would then clear the "today's" table ready for use the next day.

The data I wish copied from the today's table is only range A:J. It would be really nice to use the "Blotter date" (Today) to register in a column in the collated sheet as well as an audit of when it was added, but not essential.

HOpefully that explanation wasn't to nonsensical and many thanks for any help in advance

[AB33]

The data you have in form do not correspond to collated data columns. Need more clarity.

[YoungMacro]

HI,

Hopefully that has fixed it. In essence the headers in A:J in both sheets should be as they are in the Form.

Thanks

[AB33]

No,
You mentioned "Blotter date"
customer 1 row 7 does not match with the collated sheet of row 7. How you are getting the deal ref 2227? This figure does not exist on the form sheet,3333,3334?

[YoungMacro]

The references are arbitrary. I'm not too worried about the blotter date as it's non essential. Apologies for my long winded and confusing question but all I need is a way of copying the new data created in Form (of which there are 2 example lines)into the Collated worksheet at the first available line after the past data (of which there are a handful of example lines).

Thanks

[AB33]

Try the attached and will improve further.

[YoungMacro]

Hi, thanks for your effort. Unfortuantely the Form data seems to have replaced the data in the collated sheet rather than added it to the bottom of the existing data.

Have a nice evening

[AB33]

Here we go if you do not want to clear the data.

[YoungMacro]

HI AB33,

Thanks for that. I can see that it worked on the first test. I've tried it and it is copying over the last line of the previous data in CollatedBlotter. I suppose I need to add a +1 somewhere.

I'm also a little concerned about the line,

If.Name <> "CollatedBlotter"Then

as I was intending to create another sheet with lists and acc numbers on it, but I suppose I can reference those from a different workbook would this bypass the problem?

Thanks for the time you've spent on this

[AB33]

No, the code consolidate all sheets except the collated in to collated sheet. You only have one sheet(form) and the code copies A7-J in to next empty row in collated sheet. I suspect it is completely wrong code.