This is not a very sophisticated response, however, it will do the job.
Might be a good guide for a start.

Sub BuildTable()
Dim strWeek As String, strActivity As String, intRow As Integer, intCol
As Integer
Dim strQty As String, strRange As String

'Go to New Sheet and prepare table header
Sheets("Sheet2").Select
'Range("A1").Select 'raises error 1004 "not defined" ???
ActiveCell.Select
ActiveCell.Formula = "Desc"
'Range("B1").Select 'raises error 1004 "not defined" ???
ActiveCell.Offset(0, 1).Select
ActiveCell.Formula = "Activity"
'Range("C1").Select 'raises error 1004 "not defined" ???
ActiveCell.Offset(0, 1).Select
ActiveCell.Formula = "Qty"
ActiveCell.Offset(1, -2).Select
'Return to original data
Sheets("Sheet1").Select
'Cycle through original data
For intCol = 2 To 5 'For each column
Select Case intCol
Case 2: strRange = "B"
Case 3: strRange = "C"
Case 4: strRange = "D"
Case 5: strRange = "E"
End Select
Range(strRange & "2").Select
'Collect the column title
ActiveCell.Offset(-1, 0).Select
strWeek = ActiveCell.Text
ActiveCell.Offset(1, 0).Select

For intRow = 2 To 4 ' For each Row
Range(strRange & CStr(intRow)).Select
'Collect the row title
ActiveCell.Offset(0, -intCol + 1).Select
strActivity = ActiveCell.Text
ActiveCell.Offset(0, intCol - 1).Select
'if there is a quantity in the cell
If ActiveCell.Text < "" Then
strQty = ActiveCell.Text
Sheets("Sheet2").Select
'Range("A2").Select
While ActiveCell.Text < ""
ActiveCell.Offset(1, 0).Select
Wend
ActiveCell.Formula = strWeek
ActiveCell.Offset(0, 1).Select
ActiveCell.Formula = strActivity
ActiveCell.Offset(0, 1).Select
ActiveCell.Formula = strQty
ActiveCell.Offset(1, -2).Select
Sheets("Sheet1").Select
End If

Next intRow

Next intCol

End Sub


Good luck from Tasmania

" 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