Ok the macro that I have is working great but it only does on file at a time.
What I'm wanting it to do is go through all the files in the Subfolder and do
the rest of the macro. But I also need workbook Hard Drive Test Sheet1 to add
the new data to the end of the data that is already there for example. Once
this Marco start this first paste will be from A1 to whatever let's say A290,
I need for it to start the next paste of the next workbook at A291. Here is
the macro as I've got it wrote now.

Thanks in advance for the help.

Workbooks.Open Filename:= _
"H:\Inventory Control\HDD SCRAP\HDD SCRAP FOLDER 2009\Shipped
Pallets\2nd Qtr\PALLET 14-02.xls"
Sheets("W.Digital Pallet 5").Select
Sheets.Add
Sheets("Sheet1").Select
Sheets.Add
Sheets("W.Digital Pallet 5").Select
Application.Run "'Hard Drive test.xls'!Macro3"
Sheets("Sheet1").Select
Columns("A:F").Select
Selection.Copy
Windows("Hard Drive test.xls").Activate
Range("A1").Select
ActiveSheet.Paste
Range("A1").Select
ActiveCell.SpecialCells(xlLastCell).Select
Range("A1").Select
Range("A172").Select
Windows("PALLET 14-02.xls").Activate
ActiveWorkbook.Close
End Sub