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.

Private Sub CommandButton1_Click()

    Dim ws As Worksheet, cola As Integer, Spath, filenam, fileloc, txtfile As String

'For each workshhet loop

    For Each ws In Workbooks("SEWCUS Plot Data Example.xls").Worksheets
         
         
cola = Workbooks("SEWCUS Plot Data Example.xls").Worksheets("Sheet1").Range("Column").Value
Spath = Workbooks("SEWCUS Plot Data Example.xls").Worksheets("Sheet1").Range("Location").Value
txtfile = Workbooks("SEWCUS Plot Data Example.xls").ActiveSheet.Cells(4, cola).Value
fileloc = Spath & "\" & txtfile
filenam = ActiveWorkbook.Name
tabnam = ActiveSheet.Name

    'Open Text file

    Workbooks.OpenText Filename:=fileloc, Origin:=1256, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
    xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
    Comma:=True, Space:=True, Other:=False, FieldInfo:=Array(Array(1, 4), _
    Array(2, 1)), TrailingMinusNumbers:=True

    'Copy .txt file data

    Workbooks(txtfile).Activate
    ActiveSheet.Range("B16").Select
    ActiveSheet.Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy

    'Paste .txt file data

    Workbooks("SEWCUS Plot Data.xls").Activate
    ActiveSheet.Range.Cells(6, cola).Select
    ActiveSheet.Range(Selection, Selection.End(xlDown)).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    
    Workbooks(txtfile).Close

         
    Next ws
    
    
End Sub

Many thanks again

And merry christmas