title: Import data from multiple csv files to one Excel worksheet and calculate averages
body: <p>I’m trying to create a Macro in Excel 2010 which imports a single column of numerical data from an unknown number of semicolon delimited csv files, which are stored in a sub folder, to sequential columns in a master workbook. The macro should run from the open master workbook.</p>
  
  <p>The starting cell is known, but the size of the column range can vary.</p>
  
  <p>Every single file name is different.</p>
  
  <p>If the file name is “File name – 60.00mm.csv”, </p>
  
  <p>I wish to use “60.00” as the column  header and for it to be formatted as a number. This needs to be taken from the name of the csv workbook as it’s not part of the sheet name. I’ve managed to do this.</p>
  
  <p>Column A of the master workbook has the title “Scan number” and the rows are populated with the numbers 1, 2, 3 etc to however many rows are required for the largest data range. I’ve yet to work out how to do this.</p>
  
  <p>At a row below the last filled row of the largest data range, I want the average of all of the data above to be computed, excluding the header row. The title of this row in column A should be “Average”. I’ve workout out how to compute the average but don’t know how to output it to a row below the last filled row of the largest data range. It's currently in a cell directly below the data set.</p>
  
  <p>I’ve managed to create a macro which will find and loop through all csv files, and select and copy the relevant data range, but  I’m having trouble pasting it in the master workbook without using “Activate” and “Select”. It also skips column A and pastes to column B. </p>
  
  <p>Also, I have not gotten it to work without opening and closing each csv file sequentially.</p>
  
  <p>Can anyone please assist with improving this code and the most efficient way to provide the missing parts?</p>
  
  <p>Current Code:
      Option Explicit
      Sub Import()</p>
  
  <pre><code>Dim New_Path As String 
  Dim CSV_WB As Workbook
  Dim Data As Variant 
  Dim CSV_files As String 
  Dim lastrow_CSV As Long 
  Dim lastrow As Long 
  Dim lastcol As Long 
  Dim CSV_Sht_Name As String 
  Dim CSV_Wbk_Title As String 
  Dim averageRange As Variant 
  
  New_Path = ThisWorkbook.Path &amp; "Sub folder"
  CSV_files = Dir(New_Path &amp; "*.csv")
  
  Do While Len(CSV_files) &gt; 0
  
      Workbooks.OpenText _
      Filename:=New_Path &amp; CSV_files, _
      DataType:=xlDelimited, _
      Semicolon:=True, _
      Local:=True
  
  'Copy data.
      Set CSV_WB = Workbooks(CSV_files)
      CSV_Sht_Name = ActiveSheet.Name
      CSV_Wbk_Title = Val(Left(Right(ActiveWorkbook.Name, 11), 5))
      lastrow_CSV = CSV_WB.Worksheets(CSV_Sht_Name).Range("C" &amp; Rows.Count).End(xlUp).Row
  
      Data = CSV_WB.Worksheets(CSV_Sht_Name).Range("C14:C" &amp; lastrow_CSV).Copy
  
  'Paste Data
      Windows("Master Workbook").Activate
  
      lastcol = Cells(2, Columns.Count).End(xlToLeft).Column
  
      Cells(1, lastcol).Offset(, 1).Value = CSV_Wbk_Title
  
      Cells(1, lastcol).Offset(1, 1).Select
      ActiveSheet.Paste
  
  'Add average.
      lastrow = Cells(Rows.Count, lastcol + 1).End(xlUp).Row
  
      averageRange = Range(Cells(2, lastcol + 1), Cells(lastrow, lastcol + 1))
  
      With Cells(lastrow + 1, lastcol + 1)
          .Value = Application.WorksheetFunction.Average(averageRange)
          .Font.Bold = True
      End With
  
      CSV_WB.Close
  
      CSV_files = Dir
  
  Loop
  
  End Sub
  </code></pre>