Andy,

If all your text files are .txt files, and are in the same folder, then try
the macro below. One big assumption is that the files have data in column A
when imported, and that there aren't any empty rows or columns separating
the data. You may also need to change some of the parameters in the OpenText
command - the best way is to record a macro while opening one of your files.

HTH,
Bernie
MS Excel MVP

Sub ConsolidateTextFiles()
With Application.FileSearch
   .NewSearch
   'Change this to your directory with the text files
   .LookIn = "C:\Excel"
   .SearchSubFolders = False 'Change to True if needed
   .FileType = msoFileTypeAllFiles
   If .Execute() > 0 Then
   Set Basebook = ThisWorkbook
       For i = 1 To .FoundFiles.Count
           If .FoundFiles(i) Like "*.txt" Then
           Workbooks.OpenText Filename:=.FoundFiles(i), Origin:= _
           xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
           xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True,
Semicolon:=False, _
           Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(1, 1)
           Range("A1").CurrentRegion.Copy
Basebook.Worksheets(1).Range("a65536").End(xlUp).Offset(1, 0)
           ActiveWorkbook.Close
           End If
        Next i
       Basebook.SaveAs Application.GetSaveAsFilename("Consolidated
file.xls")
   End If
End With
End Sub