The following code can be used to get all sheets from each of the workbooks in a folder to one book and thus can be really useful when you want to consolidate a set of workbooks and want all data to be available in one workbook.
– Selva V Pasupathy
Option Explicit
Private i, j, k, lngIndex As Integer
Private dlgOpen As FileDialog
Private FileName As String
Private WS As Worksheet
Private Wkb As Workbook
Sub CombineWorkbooks()
Set dlgOpen = Application.FileDialog(msoFileDialogOpen)
With dlgOpen
.AllowMultiSelect = True
.Show
For lngIndex = 1 To .SelectedItems.Count
FileName = .SelectedItems(lngIndex)
If Right(FileName, 4) = ".xls" Then
Call Open_MoveSheets(FileName)
Else
Resume Next
End If
Next
End With
End Sub
Private Sub Open_MoveSheets(FileName)
Set Wkb = Workbooks.Open(FileName)
For Each WS In Wkb.Worksheets
WS.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
Next WS
Wkb.Close False
End Sub
