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

You would only be able to transpose 245 rows to sheet 15 if you only copy
them so that each column begins on row 1. to get all 2400 rows, for those
sheets that have that many, on sheet 15 it would be necessary to also enter
transposed groups beginning on rows 11, 21, 31, through possibly 101. The
other option would be to use up to 10 new sheets for each old sheet for which
data is transposed. There are only 256 columns across each worksheet. Are
you sure you want to do this?

"Boyd Smith" wrote:

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

On Feb 10, 9:45 pm, JLGWhiz wrote:
You would only be able to transpose 245 rows to sheet 15 if you only copy
them so that each column begins on row 1. to get all 2400 rows, for those
sheets that have that many, on sheet 15 it would be necessary to also enter
transposed groups beginning on rows 11, 21, 31, through possibly 101. The
other option would be to use up to 10 new sheets for each old sheet for which
data is transposed. There are only 256 columns across each worksheet. Are
you sure you want to do this?



"Boyd Smith" wrote:
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 -

HI JLGWhiz
I didnt do a very good job describing what Im trying to do I
apologize,
Here is a different aproach.
My data is in a Workbook"CatchAll" Worksheet Sheet1 In col A 1 Thru
Col i 1 (Nine Cols) Down to Col A2401 Col i 2401

I want to copy the first seven rows(A1:i7) on sheet 1 and pastespecial
Transpose To sheet 15 (A1:i7)
Then copy A8:i14 sheet 1 And PasteSpecial Transpose to Sheet 15
A8:I14 Etc Copy and pastespecial Each seven rows Rows down thru
A2401:i2401 Which will resulti 2743 rows total if we leave a blank
row beteween each seven rows.
There is Nine Cols Of data On sheet 1 That I wish to transpose to
sheet 15 seven rows at a time.
Blabbering on like this I must be getting tired.I hope this will be a
little clearer
Thanks Oke Doke

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

I don't see any new information here (except some misinformation), so unless
I misread it, my code should do what you describe.


A1:i7 transposed to A1:i7. I believe you meant transposed to A1:G9
A8:I14 transposed to A8:I14 I believe you meant transposed to A11:G19


At least that is what my code does. I also understood that you wanted to
process multiple sheets - which I took to mean all sheets except sheet
number 15 in the tab order, writing all their data on sheet number 15 in the
tab order.

--
Regards,
Tom Ogilvy

"Tinkerbell" wrote in message
oups.com...
HI JLGWhiz
I didnt do a very good job describing what Im trying to do I
apologize,
Here is a different aproach.
My data is in a Workbook"CatchAll" Worksheet Sheet1 In col A 1 Thru
Col i 1 (Nine Cols) Down to Col A2401 Col i 2401

I want to copy the first seven rows(A1:i7) on sheet 1 and pastespecial
Transpose To sheet 15 (A1:i7)
Then copy A8:i14 sheet 1 And PasteSpecial Transpose to Sheet 15
A8:I14 Etc Copy and pastespecial Each seven rows Rows down thru
A2401:i2401 Which will resulti 2743 rows total if we leave a blank
row beteween each seven rows.
There is Nine Cols Of data On sheet 1 That I wish to transpose to
sheet 15 seven rows at a time.
Blabbering on like this I must be getting tired.I hope this will be a
little clearer
Thanks Oke Doke

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

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

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

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

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

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

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

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

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

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

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

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

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