Merge data from all workbooks in folder based on first 3 characters of filename

Hi

I have taken the below code from someone else to merge the data from all workbooks in a folder:

Please Login or Register  to view this content.

This works fine but I have many files in the folder that have different names based on the first 3 characters (and with different dates), e.g file names are RAN02042016R, PAK01072016, ROS05042016, etc.

How can I amend the above code to open each file where the first 3 letters are the same and copy to a new workbook each time?

The end result in the example above would be to create 3 new workbooks and copy the data from all files with the names RAN, PAK and ROS respectively.

Many thanks

Hi millz,

thank you for that. How can I adjust it so that it opens each automatically and then proceeds with the macro? I have over 400 of these so ideally don't want to type each and run once.

Hi

yes that works great, thank you

However, all of the files end in "R", so I tried to amend this"

Please Login or Register  to view this content.

to this:

Please Login or Register  to view this content.

but it didn't work?

sorry I should have been clearer.

All the files that I need end in "R", there are files in the folder that do not end in "R".

I only need the files that end in "R" in the directory, but I cannot get it to work. I also tried:

Please Login or Register  to view this content.

as the files all have the same number of characters but it didn't work?

thank you, that is working fine

However, is there a way it can copy the header data in the first one (the header data is all the same in all of the files I need to copy over).