how do i transpose a worksheet with formulas and look up tables in

[Sandypants]

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

 

[Joel]

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

 

[Tyro]

Make sure to make a copy of your workbook before running the code!

Tyro

 

[Sandypants]

Hi Joel,

Thanks for this info unfortunately I really am a bit of a novice so this
does not make sense to me sorry?

If I have to copy and paste one cell at a time it will take me far too long
as I have a worksheet which is 150 rows x 200+ columns and it is growing too
large in this way, hence having to alter it to 200+ rows and 150 columns now.

If you can show me a step by step way of doing this in really simple terms
then I would really appreciate it.

Thanks again for your quick response...

Kind regs

S :0)

 

[Joel]

1) Make copy of workbook like Tyro suggested
2) Select worksheet that need to be changed
3) Copy code below below using mouse. From SUB to END SUB
4) Right click Tab on bottom of worksheet and select View Code
5) In VBA window menu, Insert - Module
6) Paste text into window using mouse (from step 3)
7) With mouse, click code in VBA window. Then Press F5 to run code.

code should transpose cells in active worksheet.


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]

Hi Joel,

Thanks for that I will give it a go.. fingers crossed I get oit right..

Thanks again

S :0)