Open Multiple Workbooks, Record names, Copy paste to Active Workbook, Close the Workbooks

Hi There,

I've been trying to figure out the best way to do this, but am getting confused!

So I have a workbook, and am looking for a piece of code which prompts the user to open files (csv) via explorer (i.e. manually clicking). The files will be in the same folder, so the user will then select several files at once (between 1 and 10).
I wish to copy some columns from the first workbook that's been opened, and paste the range into the active workbook and then perform some operations on the data.
Then I wish to copy the same columns from the second workbook, and paste them into the active workbook.
I want to run through all the workbooks that have been opened repeating this.
Once all have been used, I want to close them all (without saving).

There may be other workbooks open at the start of this process, so their names must be recorded first so that they can be ruled out of the following operations, and remain opened after the process completes.


So, to sum up in one (long!) sentence:
I'm really just looking for the code that will prompt the user to select the files (in one operation) from a folder, store their names and then be able to cycle through them in order to copy the data I want, then close them.

I hope that makes sense & fingers crossed you can help!

Hi patel45,
Sorry, the last reply/question was for you.... I assumed (perhaps incorrectly!) that you directed me to your webpage & so I also assumed your name was Ron!

Anyway, Would appreciate an answer to the above question from anyone (not just Ron! :P)

Also, this is a great solution which I can just about follow (thank you!), HOWEVER, I want the user to have the ability to pick the files using the interface (i.e. Explorer menu or equivalent). There may be 50 files in the folder, with random names, so I want him to select all the ones he wishes and THEN to proceed with the code. I think there is missing just a line or two to achieve this bit!

Hi Patel, Thanks for your reply.

Yes, I agree it is an excellent macro, but for it to work correctly, I would have to specify a folder and put ONLY the files I want to use within it.

I am looking for something slightly different... I wish the user to be prompted to manually select the files - equivalent of clicking 'Files', 'Open', and then selecting only 5 files from a folder which could contain 50 files.

Does that make sense, or am I missing something in the code on that page?

Sorry, yes it is!

I had managed to make my way through the "(next to each other)" code, which didn't use this process.

Thank you, this does exactly what I want!