Thanks Ravi! I tried running your code,but I got a 'Run time error 9 - subscript out of range error' at line 4 'y = Workbooks(..)'

Thanks,
Matt

Sub pulldata()
x = Workbooks("DM Tracker.xls").Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
y = Workbooks("Week 34.xls").Worksheets("Overall").Cells(Rows.Count, 1).End(xlUp).Row
For a = 3 To x
Workbooks("DM Tracker.xls").Worksheets("sheet2").Cells(1, 26) = "=match(B" & a & "[Week 34.xls]overall!B2:B" & y & ",0)"
b = Workbooks("DM Tracker.xls").Worksheets("sheet2").Cells(1, 26)
Workbooks("DM Tracker.xls").Worksheets("sheet2").Cells(a, 3) = Workbooks("Week 34.xls").Worksheets("overall").Cells(b, 5)
Workbooks("DM Tracker.xls").Worksheets("sheet2").Cells(a, 4) = Workbooks("Week 34.xls").Worksheets("overall").Cells(b, 29)
Next a
MsgBox "collecting data complete"
End Sub