I want to put the worksheets from several (2-5) excel workbooks (files) into one single workbook.
The names of the workbooks are not standardized and so I opted for a dialog box to select the workbooks.
Here is the code. It meets the basic requirement that all the worksheets (sheets actually) from the selected files are collected into a single file (the ActiveWorkbook
).
Sub openDialogBoxAndSelectFiles()
Dim wb1 As Workbook
Set wb1 = ActiveWorkbook
With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = True
.InitialFileName = ThisWorkbook.path & "\"
.Title = "Paddington Bear Selection Window"
.ButtonName = "Omlette"
.Filters.Clear
.Filters.Add "All Files", "*.*"
If .Show = True Then
Dim file As Variant
For Each file In .SelectedItems
Dim wb2 As Workbook
Set wb2 = Workbooks.Open(Filename:=file, ReadOnly:=True)
Dim i As Long
For i = 1 To wb2.Sheets.Count
wb2.Sheets(i).Copy before:=wb1.Sheets(1)
Next i
wb2.Close
Next
End If
End With
End Sub
The code is pretty clean (I think) for the time being and pretty much fits the bill, but that's because it doesn't do the following:
If the ActiveWorkbook
which I've defined as wb1
has blank sheets (and a wb has to have at least 1 sheet), once this process is done, I will have a few blank sheets left over.
So if file1.worksheets.count = 4
, and file2.worksheets.count = 5
, I will have a minimum of 10 (1 + 4 + 5) worksheets in the final workbook.
Questions:
- Insofar as aggregating the sheets of the files is concerned, is this a reasonable way of doing it?
- Is there a simple of ensuring there are no blank worksheets left over? Unless the input files have blank sheets that is.
Kindly,