Excel Macro to convert Columns to Rows

[jainson]

Hi, I have a series of data lined up like this

<TABLE style="WIDTH: 626pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=832 border=0><COLGROUP><COL style="WIDTH: 128pt; mso-width-source: userset; mso-width-alt: 6253" span=2 width=171><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3584" span=5 width=98><TBODY><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD class=xl63 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 128pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=171 height=17>LEVEL 3</TD><TD class=xl63 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 128pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=171>DESC</TD><TD class=xl63 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 74pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=98>4/1/2011</TD><TD class=xl63 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 74pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=98>4/4/2011</TD><TD class=xl63 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 74pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=98>4/5/2011</TD><TD class=xl63 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 74pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=98>4/6/2011</TD><TD class=xl63 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 74pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=98>4/7/2011</TD></TR><TR style="HEIGHT: 21.95pt; mso-height-source: userset" height=29><TD class=xl64 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 21.95pt; BACKGROUND-COLOR: transparent" height=29>ABCDE</TD><TD class=xl64 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Items for Lease</TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">560493576</TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">59987472</TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">59021496</TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">5475353941</TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">54795633365</TD></TR></TBODY></TABLE>

I want a macro to:

1) delete the first columns called level 3
2) and then lay down the rest of the data like this

DESC Items for Lease
4/1/2011 560493576
4/4/2011 59987472

and so on...

Thanks!

 

[Peter_SSs]

Assuming that data is in rows 1 & 2, starting in column A and that there is nothing in row 3 or in columns A:B below row 3, then try this. I suggest you test in a copy of your workbook.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Rearrange()<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    Range("A1").CurrentRegion.Copy<br>    Range("A3").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, _<br>        SkipBlanks:=False, Transpose:=<SPAN style="color:#00007F">True</SPAN><br>    Rows("1:3").Delete<br>    Columns("A:B").AutoFit<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

If that is not what you need, please provide more details.

 

[jainson]

Hi,

I think this might help. Also the excel sheet is basically an output of a query I run in MS Access so I cant have the macro in that workbook. I think my macro would also have to copy this data from the xls to xlsm and then run the above mentioned commands. Would you happen to know how to do that?

What I have for now is the following:

Dim WS1 As Workbook, WS2 As Workbook
Set WS1 = Workbook("Component_View_in_Excel").Sheets("Report_Trend")
Set WS2 = Workbook("Input_for_CRONOS").Sheets("Input")
WS1.UsedRange.Copy WS2.Cells(Rows.Count, "A").End(xlUp)

But it is giving me an error saying sub or function not defined. I am trying to run your code and this code together in one macro, is that an issue?

Thanks for your help.

 

[Peter_SSs]

You should be able to combine the macros but the lines of code you posted have a few problems ..

1. You have declared WS1 and WS2 as Workbook, but you are trying to set them to be a Worksheet. I think you should declare them as Worksheet.

2. Then in your 'Set' lines you are using the word 'Workbook'. In that situation you need 'Workbooks'
That is what is causing your 'Sub or function is not defined' error.

3. I suspect you may need to also add the blue bit below so you don't overwrite any existing data.

Dim WS1 As Worksheet, WS2 As Worksheet
Set WS1 = Workbooks("Component_View_in_Excel").Sheets("Report_Trend")
Set WS2 = Workbooks("Input_for_CRONOS").Sheets("Input")
WS1.UsedRange.Copy WS2.Cells(Rows.Count, "A").End(xlUp).Offset(1)