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

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: