Excel VBA - import multiple xlsx worksheets and append to master workbook sheet
<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>