title: VBS to compile information from multiple excel files into one
body: <p>I'm working on VBScript to move all the information from multiple excel files into one sheet on a master excel file.  </p>
  
  <p>It would basically be 1000-2000 rows of information and about 20 columns.  There would be about 5-6 total excel files in the directory.  All of the information is on the first tab, I essentially just need to copy and paste it over without overwriting the previously copy and pasted data.</p>
  
  <p>This is what I have so far, the issue I'm running into is that it copies over the previous excel sheets data in the master file with the most recent excel sheet's data.  I need it to go to the next open cell.</p>
  
  <pre><code>Const xlFilterCopy = 2
  Const xlUp = -4162
  Const xlDown = -4121
  strPathSrc = "C:\test" ' Source files folder
  strMaskSrc = "*.xlsx" ' Source files filter mask
  iSheetSrc = 1 ' Sourse sheet index or name
  'iColSrc = 1 ' Source column index, e. g. 7 for "G"
  strPathDst = "C:\test\Results\Results.xlsx" ' Destination file
  'iColDst = 1 ' Destination column index
  
  Set objExcel = CreateObject("Excel.Application")
  objExcel.Visible = True
  Set objWorkBookDst = objExcel.Workbooks.Open(strPathDst)
  Set objSheetTmp = objWorkBookDst.Worksheets.Add
  'objSheetTmp.Cells(1, iColDst).Value = "TempHeader"
  Set objShellApp = CreateObject("Shell.Application")
  Set objFolder = objShellApp.NameSpace(strPathSrc)
  Set objItems = objFolder.Items()
  objItems.Filter 64 + 128, strMaskSrc
  objExcel.DisplayAlerts = False
  For Each objItem In objItems
      Set objWorkBookSrc = objExcel.Workbooks.Open(objItem.Path)
      Set objSheetSrc = objWorkBookSrc.Sheets(iSheetSrc)
      Set objRangeSrc = objSheetSrc.UsedRange
      Set ObjSheetDst = objWorkBookDst.Worksheets.Add
      objRangeSrc.AdvancedFilter xlFilterCopy, , objSheetDst.Cells(1, 1), False
      objSheetSrc.Delete
      objWorkBookSrc.Close
  Next
  </code></pre>