Open file path from cell, paste into new sheet named from another cell and loop

Hi,

I'm a fairly basic user of VBA and have found this forum enormously useful over the years. Invariably there's already some code posted somewhere that I can find and modify to suit my needs, but this time I'm drawing a blank.

I need some VBA code to open a text file (they are text files with a *.WTS suffix, but they are text files and excel can open/import/read them no problem) and paste the contents of that text file into a new work sheet in the workbook and then loop through 225 files listed in the worksheet. I currently have a sheet in a workbook in which column A contains all of the file paths for all the files and column B contains the corresponding sheet name which I would like to apply to a new sheet into which the contents of each txt file are pasted.

I imagine this is pretty simple stuff - but I'm hitting a complete block trying to get something that even begins to work. Could anyone lend a hand?

Give this a try. This logic depends on naming the range of file names in column A. Here I have named this range as "File_Names".
There are other ways to loop down the list of file names; this is just one way.
The attached file has been edited for this macro and for the named range ("File_Names").
Note, I changed the file names & paths (in the attached file) so to work w/ my computer; you'll have to change them back for your computer. Hope this helps!

Please Login or Register  to view this content.

That works perfectly!

Thank-you!

You're welcome! Please 'Add to My Reputation' if you feel I deserve it. I did for your nicely worded problem and quick 'thank you' on the solution. Cheer!