title: VBA - Open multiple files, copy data from files to mastersheet
body: <p>I am opening many files in a folder and trying to copy data [from F10 to the end of column F (some rows may be blank) and from G10 to the end of column G (some rows may be blank)] from the files to one worksheet called "masterfile", in columns 2 and 3 respectively, under a header. I have been trying to research AdvancedFilter() and CopyRange() but cannot get it to work correctly. I am not experienced with VBA so I am having a hard time figuring out how to even correctly use them. Any advice?</p>
  
  <p>This code currently opens each file in a folder, prints the name of each file to the first column of the masterfile, and prints the information in cell J1 of the opened file down the 4th column of the masterfile. Any advice is greatly appreciated. I've been stuck for a week.</p>
  
  <pre><code>Option Explicit
  
  Sub LoopThroughDirectory()
  
      Dim objFSO As Object
      Dim objFolder As Object
      Dim objFile As Object
      Dim MyFolder As String
      Dim StartSht As Worksheet, ws As Worksheet
      Dim WB As Workbook
      Dim i As Integer
      Dim LastRow As Integer, erow As Integer
      Dim Height As Integer
  
      'turn screen updating off - makes program faster
      'Application.ScreenUpdating = False
  
      'location of the folder in which the desired TDS files are
      MyFolder = "C:\Users\trembos\Documents\TDS\progress\"
  
      Set StartSht = ActiveSheet
      Set StartSht = Workbooks("masterfile.xlsm").Sheets("Sheet1")
  
      'create an instance of the FileSystemObject
      Set objFSO = CreateObject("Scripting.FileSystemObject")
      'get the folder object
      Set objFolder = objFSO.GetFolder(MyFolder)
      i = 1
  
      'loop through directory file and print names
      For Each objFile In objFolder.Files
          If LCase(Right(objFile.Name, 3)) = "xls" Or LCase(Left(Right(objFile.Name, 4), 3)) = "xls" Then
              'print file name to Column 1
              Workbooks.Open fileName:=MyFolder &amp; objFile.Name
              Set WB = ActiveWorkbook
              'print TOOLING DATA SHEET(TDS): values to Column 2
              With WB
                  For Each ws In .Worksheets
                      StartSht.Cells(i + 1, 1) = objFile.Name
                      With ws
                          .Range("J1").Copy StartSht.Cells(i + 1, 4)
                      End With
                      i = i + 1
                  'move to next file
                  Next ws
                  'close, do not save any changes to the opened files
                  .Close SaveChanges:=False
              End With
          End If
      'move to next file
      Next objFile
      'turn screen updating back on
      'Application.ScreenUpdating = True
  
  End Sub
  </code></pre>