Macro to copy data multiple times

Hi All,

First post and i hope you can help me. I am a complete novice when it comes to coding, i have done some basics but nothing like this.

What i have is two sheets, one will provide multiple lines of data including a reference, the second sheet will provide multiple lines of data each with a reference and other information.

I need a macro to take each line in the second sheet and duplicate it as many time as there is lines in the first sheet, copy accross the reference from the first in one cell and the referece for the second into the next cell.

First sheet:
Reference
1
2
3
4

Second sheet
Reference
A
B
C
D
E

Third Sheet
Ref 2 Ref 1
A 1
A 2
A 3
A 4
B 1
B 2
B 3
B 4
C 1
C 2
C 3
C 4
D 1
D 2
D 3
D 4
E 1
E 2
E 3
E 4

I hope that makes sense, there will obviously be other data but this is my issue, any help anyof you can offer i would be very grateful and forever in your debt.

Thanks

Thanks for your help, however it does not give what i need.

I have attached two files, one is what your macro returned (Book 2) and one is what i would like it to return (Book 3)

In Book 3 you will see three sheets, sheet one haveing a reference, sheet two having a reference and then in sheet 3 is what i would like the maco to do.

Please help if you can.

Thanks

Book2.xlsmBook3.xlsx

Thanks again for your reply, however it still does not work

I enetered your code but the results, as attached are not the desired output.

Anymore help would be gratly appreciated.
ThanksBook1.xlsm

Thanks again for a swift reply, however it is still not the output i would like, your code dupliactes the rows, but puts the numbers against the numbers not letters with numbers and also overwrites the sheet 1 not placing it on sheet 3

You code in in book 1 attached and the output i want is in sheet 3 of book 3

ThanksBook1.xlsmBook3.xlsx

works perfect! you are a star and thank you so much for bearing with me