Simon

I think this gives you what you require

Sub Macro()
Dim sH As Worksheet
Dim sH2 As Worksheet
Dim i As Long
Dim j As Long
Dim RngP3 As Range
Dim RngP4 As Range
Dim RngFrom As Range

Set sH = Worksheets("Sheet1")
Set sH2 = Worksheets("Compiler")

Do While sH.Name < sH2.Name

Set RngP3 = sH.Range(sH.Range("P3"))
Set RngP4 = sH.Range(sH.Range("P4"))
Set RngFrom = sH.Range(RngP3.Address & _
":h" & RngP4.Row - 1 & ",a" & RngP4.Row _
& ":" & Cells(RngP4.Row, RngP4.Column).Address)


i = 0
For Each cell In RngFrom
i = i + 1
sH.Cells(i, j).Value = cell.Value
Next
j = j + 1
Set sH = Worksheets(sH.Range("P5").Value)
Loop
sH.Activate

End Sub


--
mudraker
------------------------------------------------------------------------
mudraker's Profile: http://www.excelforum.com/member.php...fo&userid=2473
View this thread: http://www.excelforum.com/showthread...hreadid=543596