You need to do cut and paste one cell at a time. Use this VBA code below

Sub tranpose()

Set oldsht = ActiveSheet

Worksheets.Add
Set newsht = ActiveSheet


For Each cell In oldsht.UsedRange
cell.Cut
newsht.Paste Destination:=Cells(cell.Column, cell.Row)
Next cell

oldname = oldsht.Name
oldsht.Name = oldname & "_old"
newsht.Name = oldname

End Sub


"Sandypants" wrote:

I want to basically turn around (transpose) an exsisting worksheet that has
now grown too large for the amount of columns allowed in Excel v2003. I need
to convert them to read the opposite way, i.e. Rows become columns and vice
versa. I have seen that I can achieve this by 'copy' whole sheet and 'paste
special' and 'transpose'..

However the issue is that the sheet contains huge amounts of formulas and
look up tables which take their data from a seperate tabbed sheet in the same
workbook?

How do you do this without upsetting ANY of the formulas and look ups????

Please can someone advise me of the solution preferably in very simple terms
as I am in no way an expert in this I'm afraid!!

Thanks