Create a loop in VBA to copy data out of different files

[SamT]

@snb

Should be:

      .Range("Z2:AD2").Copy TestSht.Cells(Rows.Count, 34).End(xlUp).Offset(1)
.Range("AK2:AL2").Copy TestSht.Cells(Rows.Count,39).End(xlUp)
.Range("BE2").Copy TestSht.Cells(Rows.Count,41).End(xlUp)

Hmmm... Why aren't the column letters working in that particular instance?