Excel Consolidation using vba

Multiple Excle Workbook copy paste into single work book

Luka samuel
2 min readMar 20, 2021
Excel Consolidation using vba | multiple work book copy paste into single workbook
Excel Consolidation using vba

First step : —

→ Open Excel or Spreadsheet
→ Open VBA Editor -shortcut key is (Alt + f11)
→ Insert → module

Second Step : —

Below code Copy paste in editor

Note :Double quote “ ” Format change your vba Editor

Sub lume()
Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogOpen)
fd.AllowMultiSelect = True
fd.Filters.Add “Plese select Excel file only”, “*.xl*”, 1
fd.Show
For i = 1 To fd.SelectedItems.Count
Workbooks.Open fd.SelectedItems(i)
frow = Cells(Rows.Count, 1).End(xlUp).Row
fcol = Cells(1, Columns.Count).End(xlToLeft).Column
‘findname = Left(fcol, Len(fcol) — 1)
vArr = Split(Cells(1, fcol).Address(True, False), “$”)
findname = vArr(0)
If i = 1 Then
Range(“a1:” & findname & frow).Copy
Rowf = ThisWorkbook.Sheets(“Sheet1”).Cells(Rows.Count, 1).End(xlUp).Row
ThisWorkbook.Sheets(“Sheet1”).Range(“a” & Rowf + 1).PasteSpecial
ActiveWorkbook.Close
Else
Range(“a2:” & findname & frow).Copy
Rowf = ThisWorkbook.Sheets(“Sheet1”).Cells(Rows.Count, 1).End(xlUp).Row
ThisWorkbook.Sheets(“Sheet1”).Range(“a” & Rowf + 1).PasteSpecial
ActiveWorkbook.Close
End If
Next
MsgBox fd.SelectedItems.Count & “ File Completed”
End Sub

StepThree: —

Press F5 to run program
select all workbook and see the Result

Note :

Double quote “ ” Format change

--

--