Excel Consolidation using vba
Multiple Excle Workbook copy paste into single work book
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 :
