Problem with Reshaping Excel data for Stata use.

Try:

Sub foo()
Dim i As Integer, j As Integer
Dim lRowSrc As Long, lRowTgt As Long
Dim lColSrc As Long, lColTgt As Long
Dim wsSrc As Worksheet, wsTgt As Worksheet

Set wsSrc = Sheet1
Set wsTgt = Sheet2

wsTgt.UsedRange.Offset(1, 0).Clear

lRowSrc = 2
lColSrc = 3
lRowTgt = 2
lColTgt = 9
Do Until wsSrc.Cells(lRowSrc, lColTgt).Value = ""
    For i = 0 To 5
        For j = 0 To 6
            wsTgt.Cells(lRowTgt + j, 1).Value = wsSrc.Cells(lRowSrc, 1).Value
            wsTgt.Cells(lRowTgt + j, 2).Resize(1, 7).Value = 0
            wsTgt.Cells(lRowTgt + j, 2).Offset(0, j).Value = 1
            wsTgt.Cells(lRowTgt + j, lColTgt + i).Value = wsSrc.Cells(lRowSrc + i, lColSrc + j).Value
            
        Next j
    Next i
    lRowSrc = lRowSrc + 6
    lRowTgt = lRowTgt + 7
Loop
End Sub