Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do i transpose a worksheet with formulas and look up tables in
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do i transpose a worksheet with formulas and look up tables in
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do i transpose a worksheet with formulas and look up table
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" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do i transpose a worksheet with formulas and look up table
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" wrote: 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" wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do i transpose a worksheet with formulas and look up table
Hi Joel,
Thanks for that I will give it a go.. fingers crossed I get oit right.. Thanks again S :0) "Joel" wrote: 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" wrote: 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" wrote: 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do i transpose a worksheet with formulas and look up tables in
Make sure to make a copy of your workbook before running the code!
Tyro "Sandypants" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data depiction/transpose and retain formulas | Excel Worksheet Functions | |||
transpose with formulas | Excel Discussion (Misc queries) | |||
transpose and formulas | Excel Discussion (Misc queries) | |||
How can I transpose rows to columns in a large worksheet? | Excel Discussion (Misc queries) | |||
Transpose Color Formatting to Seperate Worksheet | Excel Worksheet Functions |