This is not tested, but should set you on the right track.

HTH
GS

Sub GetData()
' Searches for specified files, opens them for editing,
' then populates wksTarget with data.
' Assumes wksTarget is Sheets("Summary"),
' and is the active sheet BEFORE opening the files.

Dim wksTarget As Worksheet, wbkSource As Workbook
Dim lLastRow As Long, i As Long, iCol As Integer
Dim cell As Range, rng As Range

'Reference the wksTarget
Set wksTarget = ActiveSheet

'Initialize the start column
iCol = 4 ' "D"

With Application.FileSearch
.NewSearch
.LookIn = ThisWorkbook.Path
.SearchSubFolders = False
.fileName = "m*.htm"
.MatchTextExactly = True
.FileType = msoFileTypeExcelWorkbooks

If .Execute() 0 Then
For i = 1 To .FoundFiles.Count
Set wbkSource = Workbooks.Open(.FoundFiles(i))
'wbkSource is now the ActiveWorkbook,
'the default sheet is the ActiveSheet.

Set rng = Range(Cells(1, "D"), Cells(Rows.Count, "D").End(xlUp))
For Each cell In rng
If cell = "Would you like to add any comments?" Then
cell.Offset(0, -3).ClearContents
End If
Next cell

'Collect the data
With wksTarget
lLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
With .Range(.Cells(34, iCol), .Cells(lLastRow, iCol))
.Formula = "=IF(ISERROR(INDEX('" & wbkSource & _
"'!$E:$E,MATCH(C34,'" & wbkSource & _
"'!$A:$A,0))),"""",INDEX('" & wbkSource & _
"'!$E:$E,MATCH(C34,'" & wbkSource & "'!$A:$A,0)))"
.Value = .Value
End With
End With

'Set next column here
iCol = iCol + 1
Next i
Else
MsgBox "No MAP Files Found; did you save in correct folder?"
End If
End With
End Sub