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
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:
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.
file1.worksheets.count = 4, and
file2.worksheets.count = 5, I will have a minimum of 10 (1 + 4 + 5) worksheets in the final workbook.
- 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.