Home Excel Powerpoint Word Outlook Error Codes

VBA Useful Codes

Welcome to the World of VBA
Home > VBAUsefulCodes

Consolidate Multiple Workbooks into One Workbook using VBA

If you have multiple workbooks with same type of column names and sheet names and you wish to consolidate all into single workbook, then you can utilize this simple code to merge all workbooks into single workbook in no time.

' 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))

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


Enter your e-mail below and get notified on the latest blog posts.


VBA Outlook KPI Excel Alt+F11 Dashboards Macros Recording Graphs Automation Developer WaterFall Powerpoint Charts Pivot Tables Forecast Charts

Follow Us