Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I transpose items from another sheet? | Excel Discussion (Misc queries) | |||
Transpose Column to Row | Excel Worksheet Functions | |||
how do i transpose a Polynomial equation from a graph to a sheet? | Excel Discussion (Misc queries) | |||
transpose column to a row? | Excel Discussion (Misc queries) | |||
How do I transpose an Excel 2003 column of data? | New Users to Excel |