creating a list from a table

**sss.seg** · 09-17-2009, 09:57 AM

Is there a way to convert a basic table, such as days of the week as column headings, months as row headings and values in each cell, into a list that would have January Monday Value
January Tuesday Value
January Wednes Value
Etc.

**JBeaucaire** · 09-17-2009, 10:06 AM

Hi, sss.seg. Welcome to the forum.

Absolutely, should be pretty straightforward. Click GO ADVANCED and use the paperclip icon to post up your workbook.

Make sure the workbook shows a desired output on a separate sheet so we can see the goal clearly.

**sss.seg** · 09-17-2009, 11:53 AM

Thanks, JBeaucaire. I've added the attachment

**StephenR** · 09-17-2009, 12:52 PM

Try this:

Sub x()

Dim rng As Range, n As Long

With Sheet1
    n = .Range("B4", .Range("B4").End(xlDown)).Rows.Count
    For Each rng In .Range("C3", .Range("C3").End(xlToRight))
        rng.Copy Sheet2.Cells(Rows.Count, "B").End(xlUp)(2).Resize(n)
        .Range("B4").Resize(n).Copy Sheet2.Cells(Rows.Count, "C").End(xlUp)(2)
        rng.Offset(1).Resize(n).Copy Sheet2.Cells(Rows.Count, "D").End(xlUp)(2)
    Next rng
End With

End Sub