Try the macro below...

It will read column F (data sheet), 78 rows at a time and write to Col 2 of
Sheet1 after transposing the values...till you run out of rows in data sheet

Sub Transpose()
Dim srcSheet As String
Dim destSheet As String
Dim i, j, lastRow As Long

srcSheet = "Data"
destSheet = "Sheet1"

Worksheets(srcSheet).Activate

With Worksheets(srcSheet)
lastRow = .Cells(.Rows.Count, "F").End(xlUp).Row
End With

lastRow = (lastRow / 78) - 1
j = 1

For i = 1 To lastRow
Worksheets(srcSheet).Range("F" & j & ":F" & (j + 77)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets(destSheet).Cells(i, 2).PasteSpecial Paste:=xlPasteAll,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
j = j + 78
Next i
End Sub


"gtslabs" wrote:


I am trying to transpose large groups of numbers from 1 sheet to
another.
I have data in sets of 78 rows. I want to get each 78 rows and put in
columns in second sheet
Can I get somehelp with my code? I get a application error 1004 error


Sub transposedata()
Dim source As Range
Dim destination As Range

For I = 1 To 130000 Step 78
source = Worksheets("data").Range(Cells(I, 6), Cells(I + 77, 6))
destination = Worksheets("Sheet1").Range(Cells(J, 2), Cells(J, 80))

Desination = source
J = J + 1
Next I

End Sub