Problem of using VBA to open a list of files and copy to 2 sheets

Now I need to use VBA to a) read all historical excel data files, b) copy all the data from these files to Sheet1 and Sheet2 respectively

I know how to open a specific file and copy the data, but i dont know how to open various of file without tpying all the files' name into the code.

Here is the code which i used for reading and copying the historical excel data files.

Please Login or Register  to view this content.

Is it only a little adjustment in this code then i can reach my requirement?

Thanks a lot!


CROSSPOST LINK: http://www.mrexcel.com/forum/excel-q...-2-sheets.html

You didn't say where the file list is located. This assumes you have full file paths in B2:B? on sheet Report

Please Login or Register  to view this content.

Sorry, i forgot to mention the file path.

the files are locate at, for example, Cell A1, and i type C:\VBA courses\Data Files in cell A1 for locating the files

Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).


CROSSPOST LINK: http://www.mrexcel.com/forum/excel-q...-2-sheets.html
------------------

I added the crosspost link for you this time, as a courtesy. Please remember in your future posts, or don't do it. This has merely been a harmless warning to underline the importance that you read and follow the Forum Rules we are all expected to abide.

In case you haven't read these rules you agreed to when you joined, stop and read them now. Thank you for your understanding and compliance.