I have this sheet with all the values in one column. Each record is six rows
of column A. Then comes a blank row. Then six more rows for the next record.
Then a blank row. Etc, Etc. for 2218 rows of column A (which is why I need a
macro).

What I want to do is copy each record (all six rows in column A for each
record) and transpose it on the next available row of the following sheet. I
have no clue how to do this. Can someone clue me?

Thanks

On Sat, 19 Apr 2008 11:14:01 -0700, anglicanista
wrote:

I have this sheet with all the values in one column. Each record is six rows
of column A. Then comes a blank row. Then six more rows for the next record.
Then a blank row. Etc, Etc. for 2218 rows of column A (which is why I need a
macro).

What I want to do is copy each record (all six rows in column A for each
record) and transpose it on the next available row of the following sheet. I
have no clue how to do this. Can someone clue me?

Thanks


Try this

Sub paste_transpose()
Application.ScreenUpdating = False
Sheets("Sheet2").Activate
ActiveSheet.Cells(1, 1).Select
For i = 0 To 316
Sheets("Sheet1").Range("A1").Offset(i * 7, 0).Resize(6, 1).Copy
Selection.PasteSpecial Transpose:=True
Selection.Offset(1, 0).Select
Next i
Application.ScreenUpdating = True
End Sub

Sheet1 is the sheet where your data is
Sheet2 is the sheet to where you want to copy

Hope this helps / Lars-Åke

"Lars-Ã…ke Aspelin" wrote:

Try this

Sub paste_transpose()
Application.ScreenUpdating = False
Sheets("Sheet2").Activate
ActiveSheet.Cells(1, 1).Select
For i = 0 To 316
Sheets("Sheet1").Range("A1").Offset(i * 7, 0).Resize(6, 1).Copy
Selection.PasteSpecial Transpose:=True
Selection.Offset(1, 0).Select
Next i
Application.ScreenUpdating = True
End Sub

Sheet1 is the sheet where your data is
Sheet2 is the sheet to where you want to copy

Hope this helps / Lars-Ã…ke


YES!!!!!

Thank you!

When I tried to do this myself, I figured the wrong number of loops but even
if I had gotten that right, I didn't know Excel syntax. Thank you for helping
me!