A few options in the attached.
You can enable lines ending in
'debug line if you want to follow what's happening as you step through the code with
F8 on the keyboard.
You can move the source data anywhere on sheet1, you can move the headers any on Sheet2, in any order, separated by blank columns if you want, but they should be on the same row.
Edit:
I see that you've asked this question elsewhere (
excel - Using a UDF in a VBA copy paste macro - Stack Overflow), you're really obliged to inform people of that.
Have a read of
Excelguru Help Site - A message to forum cross posters
Get known as a cross-poster without supplying links and you'll find your responses drying up.
ps. It would be nice if you acknowledge it when people try to help - nothing from you the last two times I tried here.