title: Open multiple excel files, copy, paste, close
body: <p>I am attempting to fix my current code to allow multiple excel workbooks to be opened at once rather than one at a time.  Currently my code runs one at a time, where I open the workbook, copy the data, paste into the macros workbook, and close the external workbook.</p>
  
  <p><strong>CURRENT CODE:</strong>  </p>
  
  <pre><code>Sub Intro()
  Dim fd As FileDialog
  Dim wkbCrntWorkBook As Workbook
  Dim wkbSourceBook   As Workbook
  Dim fNameAndPath As Variant
  
  Set wkbCrntWorkBook = ActiveWorkbook
  fNameAndPath = Application.GetOpenFilename(FileFilter:="Excel 2007, *.xls; _
      *.xlsx; *.xlsm; *.xlsa", Title:="Select File To Import")
  If fNameAndPath = False Then Exit Sub
  
  Call ReadDataFromSourceFile(fNameAndPath)
  End Sub
  
  
  
  Sub ReadDataFromSourceFile(filePath As Variant)
  Application.ScreenUpdating = False
  
  Dim n As Double
  Dim wksNew As Excel.Worksheet
  Dim src As Workbook
  Set src = Workbooks.Open(filePath, False, False)
  
  On Error GoTo CloseIt
  Dim srcRng As Range
  With src.Worksheets("Sheet1")
      Set srcRng = .Range(.Range("A1"), .Range("A1").End(xlDown).End(xlToRight))
  End With
  
  With ThisWorkbook
      Set wksNew = .Worksheets.Add(After:=.Worksheets(.Sheets.Count))
      n = .Sheets.Count
      .Worksheets(n).Range("A1").Resize(srcRng.Rows.Count, srcRng.Columns.Count).Value = srcRng.Value
  End With
  
  Dim regEx As New RegExp
  Dim GetNum As String
  Dim strPattern As String
  Dim strInput As String
  Dim strReplace As String
  Dim strOutput As String
  Dim match As Object
  
  strPattern = "^\d{0,9}\B|\b\d{0,9}(?=\s*\.)"
  
  If strPattern &lt;&gt; "" Then
      strInput = src.Name
  
      With regEx
          .Global = True
          .MultiLine = True
          .IgnoreCase = False
          .Pattern = strPattern
      End With
  
      If regEx.test(strInput) Then
          Set match = regEx.Execute(strInput)
          GetNum = match.Item(0)
          ThisWorkbook.Worksheets(n).Name = GetNum
      Else
          GetNum = ""
      End If
  End If
  
  src.Close False
  Set src = Nothing
  Exit Sub
  
  CloseIt:
  src.Close False
  Set src = Nothing
  Application.DisplayAlerts = False
  ThisWorkbook.Worksheets(n).Delete
  Application.DisplayAlerts = True
  MsgBox "ERROR!! You already entered this file"
  End Sub
  </code></pre>
  
  <p>Moreover, this code takes the numbers from the excel file name, eg. <code>"010117Siemens Hot - Cold Report.xls"</code>, so it grabs the date (010117) and names the new worksheet with the date.</p>
  
  <p>However, I am more concerned with doing something similar to the code below to make matters easier to upload files.  Which I found at this <a href="https://www.wiseowl.co.uk/blog/s209/multiselect.htm" rel="nofollow noreferrer">site</a></p>
  
  <p><strong>TEST CODE:</strong></p>
  
  <pre><code>Sub OpenSeveralFiles()
  
      Dim fd As FileDialog
      Dim FileChosen As Integer
      Dim FileName As String
      Dim i As Integer
      Set fd = Application.FileDialog(msoFileDialogFilePicker)
  
      fd.InitialFileName = "Libraries\Documents"
      fd.InitialView = msoFileDialogViewList
      fd.AllowMultiSelect = True
  
      FileChosen = fd.Show
      If FileChosen = -1 Then
          For i = 1 To fd.SelectedItems.Count
              Workbooks.Open fd.SelectedItems(i)
              Call ReadDataFromSourceFile(FileChosen)
          Next i
      End If
  End Sub
  
  Private Sub ReadDataFromSourceFile(filePath As Variant)
  
      Application.ScreenUpdating = False
  
      Dim n As Double
      Dim wksNew As Excel.Worksheet
      Dim src As Workbook
      Set src = Workbooks.Open(filePath, False, False)
  
      On Error GoTo CloseIt
      Dim srcRng As Range
      With src.Worksheets("Sheet1")
          Set srcRng = .Range(.Range("A1"), .Range("A1").End(xlDown).End(xlToRight))
      End With
  
      With ThisWorkbook
          Set wksNew = .Worksheets.Add(After:=.Worksheets(.Sheets.Count))
          n = .Sheets.Count
          .Worksheets(n).Range("A1").Resize(srcRng.Rows.Count, srcRng.Columns.Count).Value = srcRng.Value
  End With
  
      Dim regEx As New RegExp
      Dim GetNum As String
      Dim strPattern As String
      Dim strInput As String
      Dim strReplace As String
      Dim strOutput As String
      Dim match As Object
  
      strPattern = "^\d{0,9}\B|\b\d{0,9}(?=\s*\.)"
  
      If strPattern &lt;&gt; "" Then
          strInput = src.Name
  
          With regEx
              .Global = True
              .MultiLine = True
              .IgnoreCase = False
              .Pattern = strPattern
          End With
  
          If regEx.test(strInput) Then
              Set match = regEx.Execute(strInput)
              GetNum = match.Item(0)
              ThisWorkbook.Worksheets(n).Name = GetNum
          Else
              GetNum = ""
          End If
      End If
  
      src.Close False
      Set src = Nothing
  Exit Sub
  
  CloseIt:
      src.Close False
      Set src = Nothing
      Application.DisplayAlerts = False
      ThisWorkbook.Worksheets(n).Delete
      Application.DisplayAlerts = True
      MsgBox "ERROR!! You already entered this file"
  End Sub
  </code></pre>
  
  <p>In all, I am curious if there is a way to combine these methods and create a sub that opens external excel workbooks, copies the data, pastes that into a new worksheet in the macros workbook, and then close the external workbook.  Thanks in advance</p>
  
  <p><strong>I edited my current to what I have been working on to try and fix the issue, but I am running into an issue with <code>Dim regEx As New RegExp</code>.  As it is undefined?</strong></p>