Macro to insert column, populate column with filename and merge all excel files in folder

Hi everyone,

I have been struggling with the following:

I have a folder with over 100 excel files in it. I have found a macro that I have adjusted to make it merge the excel files into one worksheet:

Please Login or Register  to view this content.

What I would like to have this macro also do is insert a column in A with something like:

Please Login or Register  to view this content.

While also populating the column with the filename of the workbook the data comes from before copying it and moving on to the next file in the folder.

Any help would be great!

Activesheet.UserdRange might be better than Range("A1:IW" & lastrow) sort of range.

I have not tested this but maybe:

Please Login or Register  to view this content.

Thanks for your reply Kenneth. This does seem to do what I want in some way.It does add in the column and insert the file path and file name.

I noticed that it did only copy the top row from each file and did not open all of the files in the folder.

Any ideas?

For the first, since the row was inserted, then one needs to get the number of rows in column B. Replace the commented line and add the other.

Please Login or Register  to view this content.

As for not iterating the other files, I would have to test. I don't see why it would not import the ThisWorkbook.Path files. Obviously, you need to to change that path to what you want if different. It could error on the 2nd file so uncomment the On Error line of code. Of course if it errors, you will need to run the ending Application lines in another Sub say to reset things.

Of course it iterates all files but the macro's workbook so you need to be sure that is what you want.