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.