Add 1 row from Multiple Files into a single spreadsheet

I have a group of files on a network drive.

Directory is S:\Railserve\Availability

The Excel files all start with Availability. After Availability is the date the file was created, followed by .xlsx So example, Availability041012.xlsx

The file has multiple worksheets, but work sheet names are the same in each file. I need data from the "Car Summary By Product Line"

The data from each sheet that I would like to get from each sheet starts in A4 through I4. There are forumlas in several of the cells so would need to be a Paste Special Values type copy.

So code/macro would do this:

Copy row A4:I4 from File Availability041012.xlsx, Worksheet "Car Summary By Product Line" to a row in the Master file

Then repeat for the next file Availability041212.xlsx (this data isn't typically generated on weekends so won't be a consistent date + 1) copied to the next row down on the Master file. This would allow me to chart data for car counts from day to day. It would be really cool to have the file date in Column J so I could chart by date and show a trend, but I know beggers can't be choosers.

Thanks, Jeff

I have roughly 200 days with multipule product lines so copying and pasting each one wouldn't be feesible.

Thanks Jerry. The macro works to the point of clering the data and then nothing happens. I don't even get an error stating a macro failure issue. What can I provide to help debug this. Thanks Jeff

Ok I get an error saying the file under

Please Login or Register  to view this content.

Am I supposed to have a file name there I have tried the first file name in the list, *.*, Availability*.xlsx, etc nothing works, but when error occurs, debugger is highlighting

Please Login or Register  to view this content.

Not sure if I am doing something wrong.

Sorry Jerry, In my code I do have the quotes I just typed it to ask my question. But in the macro I have the quotes. I am new to all this forum stuff and VBA. I have never been formally trained. Thanks for being paitent with me and letting me know when I make errors.

I know that your code will work, I am just messing something up. As I step through the code, the steps execute, The highlighter gets to,

Please Login or Register  to view this content.

Then it jumps to

Please Login or Register  to view this content.

Even without the error exit line in there it just ends the run. Something I have done seems to be pushing it to go to error/end. I tried changing the number after the greather than sign, but that didn't help either. If you go back to my post from 12/6 at 4:10 pm that is how my code stands today and when I step through, that is when it jumps to the end. I appreciate your help and understand if you are done fooling with my silly request. I do appreciate your help though!. Thanks, Jeff

Jerry, Thank you. I moved the folder from my network drive to a local drive and the Macro performed perfect. I really appreciate all your help with this this will really assist me in getting the data I need.