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

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?

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

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