title: Excel VBA - import multiple xlsx worksheets and append to master workbook sheet
body: <p>I am trying to import multiple XLSX/XLS sheets into my Master Workbook's "temp" sheet, each workbooks content should be appended after the last entry.</p>
  
  <p>This works well with just one file, but I cannot seem to work out the logic behind looping through all selected workbooks. Also, considering performance and speed, is a wise to open all selected workbooks after they have been selected or should I rather open them after the import of the previous file is done?</p>
  
  <p>Would appreciate your kind help.</p>
  
  <p>EDIT:
  To be a bit more precise, the problem with the script is the part where I am supposed to look through all open workbooks. Suggestions anyone?</p>
  
  <pre><code>For Each Workbooks In lngCount
  
      With Workbooks
          .Sheets(1).Cells.Copy Destination:=MasterWB.Sheets("temp").Range("A65536").End(xlUp).Offset(1, 0)
          .Close False
      End With
  
  Next
  </code></pre>
  
  <p>Full VBA Code:</p>
  
  <pre><code>Sub import_XLS()
  Dim wb As Workbook
  Dim lngCount As Long
  
  ' speed up by turning screenupdating off
      Application.ScreenUpdating = False
  
  ' set workbooks
      Set MasterWB = ActiveWorkbook
  
      With ActiveWorkbook.Sheets("temp")
          .Visible = True
          .Cells.Delete
      End With
  
  ' Open the file dialog
      With Application.FileDialog(msoFileDialogOpen)
          .InitialFileName = ""
          .Title = "Please select the converted User Activity files for import"
          .Filters.Add "Excel Files", "*.xls; *.xlsx", 1
          '.Filters.Add "Excel Files", "*.xls", 1
          .AllowMultiSelect = True
  
          If .Show = -1 Then
               ' Open the files
              For lngCount = 1 To .SelectedItems.Count
                  Workbooks.Open .SelectedItems(lngCount)
              Next lngCount
  
              Else
                  Exit Sub
          End If
  
      End With
  
  ' open selected workbook in read only and copy all cells of worksheet 1
  
  For Each Workbooks In lngCount
  
      With Workbooks
          .Sheets(1).Cells.Copy Destination:=MasterWB.Sheets("temp").Range("A65536").End(xlUp).Offset(1, 0)
          .Close False
      End With
  
  Next
  
  ' hide temp sheet, close workbook without saving changes and free memory
      'MasterWB.Sheets("temp").Visible = False
      'wb.Close False
      Set wb = Nothing
  
  End Sub
  </code></pre>