Copy and paste/transpose each row by n times
Solved/Closed
liznguyen
-
Oct 19, 2011 at 10:29 PM
venkat1926
venkat1926
- Posts
- 1864
- Registration date
- Sunday June 14, 2009
- Status
- Contributor
- Last seen
- August 7, 2021
Related:
- Copy and paste/transpose each row by n times
- Copy and paste to last row vba - Guide
- Paste after last row vba ✓ - Forum - Excel
- How to do an automation for copy paste, added new row (macro) ✓ - Forum - Excel
- How to paste in alternate rows in excel - Forum - Excel
- Excel paste every other row - Forum - Excel
2 replies
venkat1926
Oct 20, 2011 at 05:54 AM
- Posts
- 1864
- Registration date
- Sunday June 14, 2009
- Status
- Contributor
- Last seen
- August 7, 2021
Oct 20, 2011 at 05:54 AM
data is like this from A1
Bank 2010 2009 2008 2007
bank A 1 3 4 2
bank B 1 6 9 3
try this macro
you will in column j1 down and beyond as
bank A 2010 1
bank A 2009 3
bank A 2008 4
bank A 2007 2
bank B 2010 1
bank B 2009 6
bank B 2008 9
bank B 2007 3
Bank 2010 2009 2008 2007
bank A 1 3 4 2
bank B 1 6 9 3
try this macro
Sub test() Dim rrow As Range, rcol As Range, crow As Range, ccol As Range, dest As Range Set rrow = Range(Range("a2"), Range("A2").End(xlDown)) Set rcol = Range(Range("B1"), Range("B1").End(xlToRight)) For Each ccol In rcol For Each crow In rrow Set dest = Cells(Rows.Count, "J").End(xlUp).Offset(1, 0) dest = crow dest.Offset(0, 1) = ccol dest.Offset(0, 2) = Intersect(Rows(crow.Row), Columns(ccol.Column)) Next crow Next ccol Range("J2").CurrentRegion.Sort key1:=Range("J1"), header:=xlNo Range("J2").CurrentRegion.Cut Range("J1") End Sub
Sub undo() Range(Range("J1"), Range("J1").End(xlToRight)).EntireColumn.Delete End Sub
you will in column j1 down and beyond as
bank A 2010 1
bank A 2009 3
bank A 2008 4
bank A 2007 2
bank B 2010 1
bank B 2009 6
bank B 2008 9
bank B 2007 3
venkat1926
Oct 21, 2011 at 03:36 AM
- Posts
- 1864
- Registration date
- Sunday June 14, 2009
- Status
- Contributor
- Last seen
- August 7, 2021
Oct 21, 2011 at 03:36 AM
what is SAS?
Oct 20, 2011 at 11:48 PM
Oct 20, 2011 at 11:51 PM