Brock_Hardchest
New Member
- Joined
- Feb 23, 2018
- Messages
- 27
Hello,
I am trying to transpose rows from one sheet to another sheet using a Do loop. I need it to move to the next row each time through the loop and place each transposed row in the new sheet 32 cells away from the other. The loop functions all the way through except it pastes the same transposed row over and over instead of moving on to the next row each time. Any help is appreciated. See below, thanks.
I am trying to transpose rows from one sheet to another sheet using a Do loop. I need it to move to the next row each time through the loop and place each transposed row in the new sheet 32 cells away from the other. The loop functions all the way through except it pastes the same transposed row over and over instead of moving on to the next row each time. Any help is appreciated. See below, thanks.
Code:
Sub Data()
Dim trangeQuantity As Range
bankrow = 3
newbankrow = 2
'Find the last non-blank cell in column A(1)
xrow = Cells(Rows.Count, 1).End(xlUp).Row
'Find the last non-blank cell in row 1
ycolumn = Cells(1, Columns.Count).End(xlToLeft).Column
Set trangeQuantity = Worksheets("Sheet1").Range(Cells(bankrow, 4), Cells(bankrow, ycolumn))
'adds new sheet
ActiveWorkbook.Sheets.Add.Name = "Data"
Do While bankrow <= xrow
trangeQuantity.Copy
Worksheets("Data").Cells(newbankrow, 6).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Application.CutCopyMode = False
bankrow = bankrow + 1
newbankrow = newbankrow + 32
Loop
End Sub