Month: September 2015

Get Data from Multiple Sheets in Multiple Workbooks into Master Workbook with VBA

Posted on by Dinesh Kumar Takyar

How to get data from multiple sheets in multiple workbooks into a master workbook with VBA quickly and easily. You may have twenty people working for you in the field. They collect data everyday about customers. Now we wish to consolidate this data in a master file. This master file could turn out to be useful for the company.

Watch the Excel training video before you start studying the VBA macro code:

 

VBA code:

Sub copydata()
Dim erow As Long, lastrow As Long, lastcolumn As Long

For counter = 1 To 3
Workbooks.Open Filename:=”C:\mandar-test\mandar1.xlsx”
‘Sheets(“Sheet1”).Select
Worksheets(counter).Select
lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
lastcolumn = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Range(Cells(2, 1), Cells(lastrow, lastcolumn)).Copy
ActiveWorkbook.Close

‘ Sheets(“Sheet1″).Select
Worksheets(counter).Select
erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 1).Select
ActiveSheet.Paste
Next

For counter = 1 To 3
Workbooks.Open Filename:=”C:\mandar-test\mandar2.xlsx”
‘Sheets(“Sheet1”).Select
Worksheets(counter).Select
lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
lastcolumn = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Range(Cells(2, 1), Cells(lastrow, lastcolumn)).Copy
ActiveWorkbook.Close

‘ Sheets(“Sheet1”).Select
Worksheets(counter).Select
erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 1).Select
ActiveSheet.Paste
Next
erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 1).Select
End Sub

Get Data from Multiple Worksheets in Multiple Workbooks into Master Workbook with VBA
Get Data from Multiple Worksheets in Multiple Workbooks into Master Workbook with VBA

Here’s the improved VBA code on how to copy data from multiple worksheets in multiple workbooks into a master workbook:

Sub copydata()
Dim FolderPath As String, Filepath As String, Filename As String
FolderPath = “C:\mandar-test\”
Filepath = FolderPath & “*.xlsx”
Filename = Dir(Filepath)
Dim erow As Long, lastrow As Long, lastcolumn As Long

Do While Filename <> “”
Dim wb As Workbook
Set wb = Workbooks.Open(FolderPath & Filename)
For counter = 1 To 3
‘Sheets(“Sheet1”).Select
wb.Worksheets(counter).Activate
lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
lastcolumn = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Range(Cells(2, 1), Cells(lastrow, lastcolumn)).Copy

‘ Sheets(“Sheet1”).Select
Workbooks(“copy-data-from-multiple-sheets-in-mutiple-workbooks-into-master-workbook-with-vba.xlsm”).Worksheets(counter).Activate
erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 1).Select
ActiveSheet.Paste
Next
wb.Close savechanges:=False

Filename = Dir

Loop

erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 1).Select

End Sub

Watch the video on YouTube.