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

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
---
Message posted from http://www.ExcelForum.com/

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

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

--
Regards,
Tom Ogilvy


"rbelforti " wrote in message
...
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
---
Message posted from http://www.ExcelForum.com/

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

Thanks for the repsonse Tom

I've gotten the code to work for a set number of rows, but is there a
way that I can have the loop repeat as many times as there is data or
until it reaches the first row that is blank. I would like to know so
that when I add or delete rows I do not have to modify the macro as
well.
Any thoughts???

Bob Belforti


---
Message posted from http://www.ExcelForum.com/