On Feb 12, 12:59 am, "Tom Ogilvy" wrote:
If it skips down 200 lines, it means you have cells in your sheets that
appear blank, but actually aren't - since lastrow is established at the end
of these rows, it appears you are getting blank rows for output - however
you are getting what is in your sheets - likewise for any random columns -
these would again be rows on your original sheet. This assumes you don't
have merged cells which would be problematic.

Nothing in the code to cause the focus to change - so if it does that, it is
part of the pastespecial command.

You can put

Application.ScreenUpdating = false ' at the beginning of the code

--
Regards,
Tom Ogilvy

"Oke Doke" wrote in message

ups.com...



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

groups.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

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

- Show quoted text -

Hi
I put up a post but I dont Think It posted So Ill do it again if it
did post just discard
Your suggestion to change the line to set sh didnt do anything so
what I did I put everything into a new workbook and of course a new
worksheet an it started copying to sheet 15 so Im guessing that my
workbook ,Worksheet was corrupted to the point sheet 15 wasnt
reconized However there is a couple of problems..

While the macro is running the thing flutterd between sheet1 and
sheet15 with my limited knowledge of vba I think that can be fixed
with a screen update something like that.

It copys to sheet 15 all the way it is supposed to then it will jump
down about 200 rows and start to paste some columns Just a col here
and two or three there never a full 9 columns It does this until
about 20000 rows and then it will stop.The columns that is scettred is
different they are not transposed,That one I wouldnt to venture a
guess For A Fix- Hide quoted text -

- Show quoted text -

Thank you very much for your excellent Help with the Transpose Code
Have A nice Day
Oke Doke