VBA to organise data into specific rows, using row headers

Hi all,
I have searched through every forum and thread and could not find anything similar to what I am after. If I have missed it then please redirect me.

I have an excel file that contains data for lubrication oils. This data is copied from a website which posts up the results. However the results arent always in the same order so when copied into the excel file the row order in one sheet does not match the row order in the second sheet. This would be easy to re-organise if there was only a few results but you are talking hundreds of sheets, most of them with different row orders.

I have created a master row list that I would like to implement throughout the rest of the sheets organising all the data to match the order of this master list (this is to make autogenerating reports easier).

I have attached a simplified example workbook which will explain what I mean Example.zip

Thanks

Hi Stephen, try it (see attachment)

see also here

You Sir are a star, this is exactly what I meant. Thank you very much

Quick question Nilem, The code is great but I would like to ignore row 1 on every sheet. As row 1 contains different headings for each sheet. That way the code does not ovewrite cell A1 with "SAMPLE" everytime I run it.

Thanks

change this line in the code

ReDim y(1 To ub, 1 To UBound(x, 2)): y(1, 1) = x(1, 1)

to

ReDim y(1 To ub, 1 To UBound(x, 2))

and change this line

.Range("A1").Resize(ub).Value = arr

to

.Range("A1").Resize(ub).Value = arr: .Range("A1").Value = x(1, 1)

That did the trick, thanks Nilem