Macro to take columns and paste them into seperate rows

[garretht]

Hi All,

I've searched a number of threads for a solution and tried a few, but can't seem to get it to work on my sheet.

I have a sheet with 2 static columns which give a Description and a code that matches the description. In all of the remaining columns, I have a header, which is the individual profit centre, and in the detail, it has the amount linked to the code in the row and the profit centre in the column.

I want to take all the columns containing the profit centres and the corresponding amounts and bring both of these into new rows. The original table looks something like this:

Column A Column B Column C Column D
GL Description GL Code Profit Centre 1 Profit Centre 2 etc...
Admin Costs 145503 1478 532


What I want it to look like is as follows:

Column A Column B Column C Column D
GL Description GL Code Profit Centre Amount
Admin Costs 145503 Profit Centre 1 1478
Admin Costs 145503 Profit Centre 2 532

I had about 180 columns with profit centres and amounts going across and 223 unique GL codes and descriptions running down. I would appreciate any assistance.

 

[MickG]

Try this:-
Results sheet2

[COLOR="Navy"]Sub[/COLOR] MG27Oct41
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Lst [COLOR="Navy"]As[/COLOR] Long: ray [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] Lcol [COLOR="Navy"]As[/COLOR] Long: Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
c = 1
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
Lcol = ActiveSheet.UsedRange.Columns.Count
ReDim ray(1 To Rng.Count * Lcol, 1 To 4)
ray(1, 1) = "GL Description": ray(1, 2) = "GL Code": ray(1, 3) = "Profit Centre": ray(1, 4) = "Amount"
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    Lst = Cells(Dn.Row, Columns.Count).End(xlToLeft).Column
    [COLOR="Navy"]For[/COLOR] Ac = 3 To Lst
        c = c + 1
        ray(c, 1) = "Admin Cost"
        ray(c, 2) = Dn(, 2)
        ray(c, 3) = "Profit Centre " & Ac - 2
        ray(c, 4) = Dn(, Ac)
    [COLOR="Navy"]Next[/COLOR] Ac
[COLOR="Navy"]Next[/COLOR] Dn
Sheets("Sheet2").Range("A1").Resize(c, 4) = ray
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[garretht]

Hi Mick,

Thanks a mil for your response. I get a weird error though when I run the macro. It says "Compile error: Statement invalid outside Type block". It then highlights the Sub MG27Oct41 line in yellow. I do notice that when I paste the code, it adds a () parenthesis behind this line which is not in your original code. I'm not sure if this has any impact? Also, it selects the 'Dim Lst as Long: ray As Variant' row.

(I'm very new to macros, so my apologies if some of this is very basic).

Thanks again,
Garreth

 

[MickG]

That line is the sub Name, if you want to run it under your own sub name then delete the line "Sub MG27Oct41 "
You may also find you then have 2 "End Subs", delete on of those also!!