Macro to copy data from various locations repeating down a list

Morning all,

I need to produce a list from an imported PDF file in Excel. The list has multiple accounts on it showing what they have bought in a store. This results in some people having one line and some with 20 etc. I need all of this data in a clean list with all the relevant data added to the end columns. I have attached an image showing the template and what I need.

I need B10 copied to col U against each row of data (where there are transactions only), I need the blank cells under the dates populated with the date from above (only where there's a transaction) and the name and form as well (as per the colour coding). I could record a macro to do this for the first entry but I don't know how to repeat it down the entire sheet (8000 rows) which have different number of rows in each set of data.

Can anyone help please?
Thanks
James

Hi WBD,

That's perfect! Thank you. I ran it on my full workbook and it worked perfectly. Much appreciated

James

WBD

You helped me out on this macro back in April and it's worked since then but it's suddenly stopped working. I tried using it on the original file and it doesn't do what it used to do... it's doing most of the macro but it's not bringing all of the data together and putting all of the rubbish rows/columns at the end. As the macro and original spreadsheet haven't changed im not sure if something in excel has changed? Any ideas?

Thanks James

Here is a test data sheet. Thanks Test data.xlsm

Test data v2.xlsm

I have attached a new sheet. 1 tab is the one you sent and the other is how it used to look... i.e. sorted so the data is together and the rest is at the bottom.