Using the macro below, I was able to open files sequentially in a directory listing and select the target cell to be copied. However, the cell kept being overwritten and what I wanted was to have an output workbook which comprised a copy of all the entries. I couldn't figure out how to index my cell to a new position to avoid being overwritten.

Any help you are able to provide would be invaluable?

Dim Counter As Integer

Opener = "d:\Projects\Futureact\Q4 Responses\openandcopy.xlsm" 'file i would like to contain all the output results
FileDir = "d:\Projects\Futureact\Q4 Responses\" 'directory containing all the workbooks
FileSearch = "*.xls"
fileName = Dir(FileDir & FileSearch)
Counter = 0
Do While fileName <> ""
Workbooks.Open fileName:=FileDir & fileName
Range("B3:E3").Select 'unmerge cells
Selection.MergeCells = False
Range("B3").Select ' what to copy
Selection.Copy
Windows("OpenandCopy.xlsm").Activate ' name of the file to consolidate data

Counter = Counter + 1 ' set it up so that each B3 cell is copied on a different row
' Range("Counter").Select ' don't know how to do it
ActiveSheet.Paste
Range("B4").Select

Workbooks(fileName).Save 'move onto the next excel file to access data
Workbooks(fileName).Close
fileName = Dir()

Loop
End Sub