New Member
February 14, 2019
Data comes in columns and I need rows. I used the =A1 then =A2 then =A3 etc. Very simple
Now i want to copy that row and past it to handle the rest of the columns. I need the paste function to choose B1 (the next horizontal row) and not A2 (the next vertical cell) as is the default.
Is it possible to force Excel to paste horizontally instead of vertically?
Thanks,
Dave
VIP
Trusted Members
June 25, 2016
New Member
February 14, 2019
Hi Sunny,
Thank you for the reply. I have transposed and I even made a macro that will transpose over and over for me. These are limited as transposing one at a time is tedious and the macro is limited to how many times I record.
My question is about pasting formulas. When we create a formula and then paste that formula for the additional rows, Excel assumes that you want to change the formula for each row in a vertical (A1, A2, A3) fashion.
As you can see in my attachment. I manually used the =A1, =A2, A3 etc. to create my first row. Now I want to copy and paste that as many times as I like but excel is pasting the 2nd row as A2, A3, A4.
I want to Force Excel to paste this as B1, B2, B3. The next row would be C1, C2, C3 and so on.
Is there a way to force Excel to past right to left instead of up and down?
VIP
Trusted Members
June 25, 2016
VIP
Trusted Members
June 25, 2016
If you need a macro then maybe you can try this.
Just make sure that there is nothing under the data. The number of rows in your data must be consistent i.e. 7 rows etc
The macro check the number of rows by looking at column A. The StartRow tells the macro which row to start pasting the data (I have set it to 15 in this example)
Sub TransposeData()
Dim LastColumn As Long
Dim LastRow As Long
Dim c As Long
Dim r As Long
Dim StartRow As Long
LastColumn = Cells(1, Columns.Count).End(xlToLeft).Column
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
StartRow = 15
Application.ScreenUpdating = False
For r = 1 To LastColumn
For c = 1 To LastRow
Cells(StartRow + r, c) = Cells(c, r)
Next
Next
Application.ScreenUpdating = True
End Sub
Good luck.
Sunny
1 Guest(s)