Subscript out of range error

H All,

I am trying to transfer data from 'Week 34.xls' to 'DM Tracker.xls' based on matching Telephone numbers in Col B of 'Week 34.xls' and Col B of 'DM Tracker.xls',but I receive a 'Runtime error 9 : Subscript Out of Range' error for the following data transfer code at line 3. Can anyone guide me with this?

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

Thanks,
Matt

Assuming those workbooks and thoise sheets exist, try

Please Login or Register  to view this content.