Sub MyCopyMacro()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim lc As Long
Dim lr As Long
Dim r As Long
Dim c As Long
Dim num As Long
Dim nr As Long
Application.ScreenUpdating = False
' Set source worksheet
Set ws1 = Sheets("Sheet1")
' Set desintation worksheet
Set ws2 = Sheets("Sheet2")
' Find last row with data on source sheet
lr = ws1.Cells(Rows.Count, "A").End(xlUp).Row
' Find last column in header row on source sheet
lc = ws1.Cells(1, Columns.Count).End(xlToLeft).Column
' Determine number of months
num = (lc - 1) / 2
' Put headers on destiantion sheet
ws2.Range("A1") = "Product"
ws2.Range("B1") = "Month"
ws2.Range("C1") = "Volume"
' Set first row to place data on destination sheet
nr = 2
' Loop through all data row on source sheet
For r = 2 To lr
' Loop through month columns
For c = 2 To (num + 1)
' Check to see if month is not 0
If ws1.Cells(r, c) <> 0 Then
' Populate product
ws2.Cells(nr, "A") = ws1.Cells(r, "A")
' Populate month
ws2.Cells(nr, "B") = ws1.Cells(r, c)
' Populate volume
ws2.Cells(nr, "C") = ws1.Cells(r, c + num)
' Increment row counter
nr = nr + 1
End If
Next c
Next r
Application.ScreenUpdating = True
End Sub