Log In
Register
Home
New Member
February 14, 2019
Offline
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
Offline
Hi David
You could copy the required column and then use Paste - Paste Special - Transpose to do it column by column.
If you require a formula then one example is attached.
Hope this helps.
Sunny
New Member
February 14, 2019
Offline
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
Offline
At least now with your attachment we can see what you actually wanted.
I am not aware of any way that the formula can become B1, C1 etc when A1 is copied downwards.
There are other workarounds using OFFSET etc.
See if my example helps.
Sunny
VIP
Trusted Members
June 25, 2016
Offline
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)
