Bob Philips recently posted this in response to a similar question. Perhaps
you can adapt it to your situation:

Sub ProcessFiles()
Dim oFSO As Object
Dim i As Long
Dim sFolder As String
Dim fldr As Object
Dim oFolder As Object
Dim oFile As Object
Dim oFiles As Object
Dim this As Workbook
Dim iRow As Long
Dim oSh As Worksheet
Dim rng As Range

Set oFSO = CreateObject("Scripting.FileSystemObject")

Set this = ActiveWorkbook
Set oSh = ActiveSheet
sFolder = "C:\MyTest"
If sFolder < "" Then
Set oFolder = oFSO.GetFolder(sFolder)
Set oFiles = oFolder.Files
For Each oFile In oFiles
If oFile.Type = "Microsoft Excel Worksheet" Then
Workbooks.Open FileName:=oFile.Path
With ActiveWorkbook
Set rng = _
.Worksheets(1).Range("A337:A383") _
.SpecialCells(xlConstants)
iRow = oSh.Cells(Rows.Count, 2).End(xlUp)
If iRow < 1 Then iRow = iRow + 1
rng.Copy Destination:=oSh.Cells(iRow, 2)
.Close SaveChanges:=False
End With
End If
Next oFile

End If ' sFolder < ""

End Sub



--
Regards,
Tom Ogilvy

"JT" wrote in message
...
I used the macro copy function and created the following:

Workbooks.Open ("C:\2001.xls")
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Copy
Windows("Macro_try2").Activate
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.Offset(columnOffset:=-1).Activate
ActiveCell.FormulaR1C1 = "'2001"
ActiveCell.Offset(columnOffset:=1).Activate
Range(Selection, Selection.End(xlDown)).Select
Windows("2001.XLS").Activate
ActiveWorkbook.Close
Windows("Macro_try2.XLS").Activate
Selection.End(xlDown).Select
ActiveCell.Offset(rowOffset:=1).Activate

This works great for one file, but I have a directory with over 200 files
that need the same action.

How do I select the next file in the directory after finishing with one?

As you can see the file name is used in 3 different lines in the macro.
How
do I go from one file to the next and update the macro with the current
file
name?

I would like to be able to copy the file name down the column it is in for
all rows out of that file, but even the relative copy function pastes data
into a specific range. What code would I use to set the paste range equal
to
the number of rows copied?

Any guidance is appreciated.