This Worked fine for me:

Sub ConsolidateData()
Dim sh As Worksheet
Dim sh1 As Worksheet
Dim i As Long, rw As Long
Dim lastrow As Long, rng As Range
Set sh1 = Workbooks("Catchall.xls").Sheets(15)
rw = 1
For Each sh In Workbooks("Catchall.xls").Worksheets
If sh.Name < sh1.Name Then
lastrow = sh.Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To lastrow Step 7
Set rng = sh.Cells(i, 1).Resize(7, 9)
rng.Copy
sh1.Cells(rw, 1).PasteSpecial _
xlPasteAll, Transpose:=True
rw = rw + 10
Next i
End If
Next sh
End Sub


--
Regards,
Tom Ogilvy


"Boyd Smith" wrote in message
et...
Hi
I am running excel xp on windows xp.
I Have Lots of data on lots of worksheets.All of the data is numbers In 9
cols, A thru I, Most of them have 2400 + rows tho some can be less than
2400 There is No formulas but some of the Cells Have A background color so
I need to keep that.
What Im trying to do is copy seven rows and paste special transpose to
sheet 15 A1, Go back get the next seven rows and transpose them to sheet
15 A9 Leaving an empty row ,.Next Seven Rows Transpose to A17 ,Next A25
etc To The Last Row of the sheet;
In other words Im trying to copy each seven rows on a sheet and transpose
to sheet 15 in the same workbook Leaving An Empty row Beteween each seven
rows
By the way workbook name "Catchall "
I want to repeat this all the way to the last row,
I hope this makes sense to someone that can help me,
Thanks be to all
Oke Doke