Excel VBA - How to transpose every fifth row to same column on another sheet

[rbelforti]

I'm trying to automate the following code into a do while loop or for
loop but cannot figure out how to reference a range with variables
instead of the exact cell names.

I want to take Range("B2:M2"), Range("B6:M6")., Range("B10:M10"),
Range("B14:M14") etc and copy them to one large column on another
sheet. (the column should grow by a factor of 12 rows each time.)

Please give some advice on how to use a loop to automate the following
code:

I recorded the Macro called "Transpose"

Sub Transpose()
'
' Transpose Macro
' Macro recorded 6/24/2004 by Robert Belforti
'

'
Range("B2:M2").Select
Selection.Copy
Sheets("Sheet1").Select
Range("B2").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone,
SkipBlanks:=False _
, Transpose:=True
Sheets("Sheet2").Select
'
Range("B6:M6").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("B14").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone,
SkipBlanks:=False _
, Transpose:=True
Sheets("Sheet2").Select
'
Range("B10:M10").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("B26").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone,
SkipBlanks:=False _
, Transpose:=True
End Sub

Attachment filename: bowling_chart_test.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=592563

 

[Tom Ogilvy]

Sub Transpose()
Dim j as Long, i as Long
j = 2
for i = 1 to 14 Step 4
Sheets("sheet1").Cells(i,2).Resize(1,12).Copy
sheets("Sheet2").Cells(j,2).Paste Special Paste:=xlAll, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=True
j = j + 12
Next

End Sub