title: VBA Import multiple sheets into Workbook
body: <p>I have the following code which allows me to select a single file and import the "Pens" tab from it, however I would like to select multiple files.</p>
  
  <p>I would like to be able to select the "Pens" tab from multiple workbooks each on its own tab in the consolidated workbook. </p>
  
  <p>Could you please assist in how this might work? I think this might require the use of the For Each function but not sure how to structure this.</p>
  
  <p>Thanks very much in advance</p>
  
  <pre><code>Sub ImportActiveList()
      Dim FileName As String
      Dim WS1 As Worksheet
      Dim WS2 As Worksheet
      Dim ActiveListWB As Workbook
  
      Set WS2 = ActiveWorkbook.Sheets("AllPens")
      FileName = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls*),*.xls*", _
                                             Title:="Select Active List to Import", _
                                             MultiSelect:=False)
  
      If FileName = "False" Then
          Exit Sub
      Else
          Set ActiveListWB = Workbooks.Open(FileName)
      End If
  
      Set WS1 = ActiveListWB.Sheets("Pens")
  
      WS1.UsedRange.Copy WS2.Range("A1")
  
      ActiveWorkbook.Close False
  
  End Sub
  </code></pre>