' Consolidating Multiple Same type of workbooks into one workbook.
' It is necessary that all of the workbooks have same sheet name and same type of columns
' Data Can be different


Sub Consolidate_multiple_workbooks_into_one()

Dim wbSource As Workbook
Dim wbTarget As Workbook

Dim fddest As FileDialog
Dim FileChosendest As Integer
Dim FileNamedest As String

Dim fd As FileDialog
Dim FileChosen As Integer
Dim FileName As String

shtnm = "Data"
initialpath = "C:\"

Set fddest = Application.FileDialog(msoFileDialogFilePicker)
fddest.Title = "Select Destination File"
'use the standard title and filters, but change the
'initial folder
fddest.InitialFileName = initialpath
fddest.InitialView = msoFileDialogViewList
'allow multiple file selection
fddest.AllowMultiSelect = False

FileChosendest = fddest.Show
If FileChosen = 0 Then
'open each of the files chosen


Set wbTarget = Workbooks.Open(fddest.SelectedItems(1))

Else
MsgBox "File Not Selected"
End If

Dim i As Integer
Set fd = Application.FileDialog(msoFileDialogFilePicker)
fd.Title = "Select Source Files (If Multiple select all files)"
'use the standard title and filters, but change the
'initial folder
fd.InitialFileName = initialpath
fd.InitialView = msoFileDialogViewList
'allow multiple file selection
fd.AllowMultiSelect = True

FileChosen = fd.Show
If FileChosen = -1 Then
'open each of the files chosen
For i = 1 To fd.SelectedItems.Count
Set wbSource = Workbooks.Open(fd.SelectedItems(i))

' Set the Range to be copied from source to destination
destlastrow = wbTarget.Sheets(shtnm).Range("A" & Rows.Count).End(xlUp).Row + 1
srclastrow = wbSource.Sheets(shtnm).Range("A" & Rows.Count).End(xlUp).Row
wbSource.Sheets(shtnm).Range("A2:C" & srclastrow).Copy

wbTarget.Sheets(shtnm).Range("A" & destlastrow).PasteSpecial
wbSource.Close False
Application.CutCopyMode = False
Next i
wbTarget.Close True

End If
MsgBox "Completed"
End Sub