Hi guys
I'm trying to write a short piece of code to prevent me from having to open 200+ .txt files and copy& paste the data into excel!
Basically I have a workbook with 15 sheets in. Each sheet is laid out exactly the same.
In each sheet I need to copy and paste data from .txt files into it in seperate columns The .txt files are in 10 different folders (the .txt in thses folders are all named the same, They are outputs from various model runs).
What I require is a macro that for each worksheet in my workbook, will open a .txt file from a folder, the location of which I can insert in a cell. Copy the data from a column and then paste it into the column that I specify in worksheet.
What I envision is:
1) Manually insert into a master cell, on a master sheet, the column number relating to the column I need the data to be inserted into in each of the worksheets (Cola)
2) Manually Insert the Folder address where the text files are located into a master cell (Fileloc)
3) Command button on the master sheet that when pressed.....
4) the macro will go to the first worksheet get the .txt file name from the cell (4,Cola)
5) open the .txt file based on the .txt filename and location (Fileloc)
6) copy the data from column 2 of teh .txt file and paste into the worksheet cell (6, Cola)
7) then repeat the process for each worksheet, except for the mater sheet which has the cells that I have put the FileLoc and Cola numbers into.
I can then manually adjust the file address and column number and just keep running the macro until all of my columns are filled. Da Dah!!!!
I hope this makes sense and I'd really appreciate any help. I've had a go at putting some code together from other bits and pieces but it doesn't work. I'll try and attach an example of the workbook and code.
p.s. it wont let me attach workbook at the moment but I'll try again later. Not sure the code alone will be much use.
Please Login or Register to view this content.
Many thanks again
And merry christmas
Bookmarks