Select the cells and run this macro before transposing.

Sub Absolute()
Dim Cell As Range
For Each Cell In Selection
If Cell.HasFormula Then
Cell.Formula = Application.ConvertFormula _
(Cell.Formula, xlA1, xlA1, xlAbsolute)
End If
Next
End Sub


Gord Dibben MS Excel MVP

On Mon, 19 Jan 2009 13:33:01 -0800, mickn74
wrote:

Jarek

I realised once I posted my last response this didnt fix my problem as
Transpose will copy a formula or value into the cell and go across the row
yet the formula looks at different cell references, yet I need it to be
referencing the calculation in the cell as it will be changing all the time.

Eg Cell C138 (=M162), D138 (=M163), E138 (=M164) etc etc

Is there some fast way to make the cell $M$162 without having to manually
reference each cell

thanks

"Jarek Kujawa" wrote:

do you mean this?

select M162:M166
Edit-Copy (or CTRL+C)
go to C138
Edit-PasteSpecial-select Transpose

etc.

On 19 Sty, 07:59, mickn74 wrote:
Without manually having to enter every cell reference I need ot copy multiple
cells from a column into various rows

Eg : I want to copy
Comunm M : M162:M166 (M168:M172 ...............) etc
to Row 138 : C138, D138, E138, F138, G138
Row 139 : C,D,E,F,G
Row 140 etc

Is there a quick copy and paste method for this

Or do you have to refernce every cell

Regadrs,

Michael

Regadrs,

Mick