One more...

Option Explicit
Sub testme()
Dim NewWks As Worksheet
Dim CurWks As Worksheet
Dim iRow As Long
Dim iCol As Long
Dim oRow As Long

Set CurWks = Worksheets("Sheet1")
Set NewWks = Worksheets.Add

NewWks.Range("a1").Resize(1, 3).Value _
= Array("Desc", "Activity", "Qty")

oRow = 1
With CurWks
For iRow = 2 To .Cells(.Rows.Count, "A").End(xlUp).Row
For iCol = 2 To .Cells(1, .Columns.Count).End(xlToLeft).Column
If Trim(.Cells(iRow, iCol).Value) = "" Then
'do nothing
Else
oRow = oRow + 1
NewWks.Cells(oRow, "A").Value = .Cells(1, iCol).Value
NewWks.Cells(oRow, "B").Value = .Cells(iRow, "A").Value
NewWks.Cells(oRow, "C").Value = .Cells(iRow, iCol).Value
End If
Next iCol
Next iRow
End With

NewWks.UsedRange.Columns.AutoFit
End Sub


wrote:

I want to convert a table to a list
I am still struggling with VBA.
I have not been able to find a similar example.
Can anyone help ?

Thank you.

Wayne.

Input table - WorkSheet A
-------------------------
Week1 Week2 Week3 week4
Activity 1 1
Activity 2 2 3
Activity 3 2 5 4

Wanted output list on WorkSheet B

Desc Activity Qty
-----------------------------------
Week 1 Activity 1 1
Week 1 Activity 3 2
Week 2 Activity 2 2
Week 3 Activity 3 5
Week 4 Activity 2 3
Week 4 Activity 3 4

--

Dave Peterson