In A5 put in the formula

=OFFSET(Sheet1!$A$1,TRUNC((ROW(A1)-1)/50)+1,COLUMN()-1,1,1)

drag fill to column C

In D5 put in the formula
=OFFSET(Sheet1!$E$1,MOD(ROW(A1)-1,50)+1,COLUMN()-4,1,1)

drag fill to column G

then select A5:G5 and drag fill down 2500 rows.

for a macro

Sub FillWithData()
Dim rng as Range
Range("A5").Resize(2500,3).Formula = _
"=OFFSET(Sheet1!$A$1,TRUNC((ROW(A1)-1)/50)+1,COLUMN()-1,1,1)"
Range("D5").Resize(2500,3).Formula = _
"=OFFSET(Sheet1!$E$1,MOD(ROW(A1)-1,50)+1,COLUMN()-4,1,1)"
set rng = Range("A5").Resize(2500,6)
rng.Formula = rng.Value

End Sub

--
Regards,
Tom Ogilvy



"Michael168 " wrote in message
...
Hi!,Tom Ogilvy

Your second time formula works perfectly except when I try to use
header rows in sheet2 the formula cannot shift accordingly.

Can you guide me again,says I want the formula to start from A5 in
sheet2 without any changes in sheet1?

Is it hard to code the routine in VBA?

Thanks
Michael.


---
Message posted from http://www.ExcelForum.com/