The first code you sent walked thru all the sheets copied
and pasted somewhere but nothing went sheet 15

I have only posted the first code. It went to the 15th sheet in the tab
order.

----------------
Subscript out of range means you don't have 15 sheets in the workbook. If
you want to copy to a sheet named Sheet 15, then change

Set sh1 = Workbooks("Catchall.xls").Sheets(15)

to

Set sh1 = Workbooks("Catchall.xls").Sheets("Sheet 15")


--
Regards,
Tom Ogilvy



"Oke Doke" wrote in message
oups.com...
On Feb 11, 12:25 pm, "Tom Ogilvy" wrote:
If this doesn't do that, then explain where it fails.

--
Regards,
Tom Ogilvy

"Tinkerbell" wrote in message

ps.com...



On Feb 10, 11:44 pm, "Tom Ogilvy" wrote:
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

news:bNmdncsdP9NU91PYnZ2dnUVZ_vOlnZ2d@northstate .net...

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- Hide quoted text -

- Show quoted text -

Mr Ogilvy
Im Sorry For The Confusion I Caused I Guess I still Didnt Clearly
Describe What I want To Do With this Data, Heres Another Try.

I have lots of WorkSheets in A Workbook Named "Catchall".

On each Sheet The Data Is in 9 Columns(A Thru I)

On Each Sheet I Have Over 2000 Rows Of Numbers
These Rows Were Put on the Sheet Seven Rows At A Time As A Group And
Must Be Kept Together, Thus I Need To Transpose Them As A group of
Seven Rows To Sheet 15 .I need An Empty Row Beteween Each group of
Seven Rows If possible If Not I Can Do it Later

There is so Many Sheets That I Have to do the transpose One Sheet At A
time Else Ill lose Track Of Where Everything Is .

In DataBase Speak You Would Say Each Seven Rows Is One Record

Hope This Clears Up Some The Late Nite Babbling I Did Last Nite

Oke Doke- Hide quoted text -

- Show quoted text -

Runtime Error 9
Subscript out of range
Fails
Set sh1 = Workbooks("Catchall.xls").Sheets(15)
----Line Turns Yellow
I would have No Idea where to start fixing that
Just a note :The first code you sent walked thru all the sheets copied
and pasted somewhere but nothing went sheet 15