Sheet Combining Macro

[gripper]

I have been poking around on this site trying to fine the VBA code to do the following:
Merge data from multiple workbooks in a folder into a master worksheet.

I have a folder with about 10 single sheet workbooks.
I want to combine all the worksheets from each workbook into a single worksheet on a new workbook – Sheet 1.

I would like to just append the data into the columns I can sort later – I do not need separate worksheets within the master.

Each workbook basically has 3 columns and rows of data from 2 to 1000. During the process I would like to have column one in its normal place but then in column 2 I would like to add the path (no hyperlink) of where that data came from (Ie c:\data\file1.csv) and then move the old column 2 and 3 to the new position of 3 and 4.

Basically I will need to be able to select the folder path or some other way for the VBA to target the correct folder.

I don’t want to delete the original files though.

Any help would be greatly appreciated.

 

[gripper]

Please disregard my last post. I kept digging and found some solutions and a microsoft tool add in that will combine all workbooks I need automatically. I am not sure if external links are approved by the forum rules or not but I found a MS Excel Code at http://msdn.microsoft.com/en-us/library/cc837974.aspx

And also on that page there is a free add-in tool http://www.rondebruin.nl/merge.htm

I want to thank all of you for the support I have recieved over the last few years.