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
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.
Thanks for Vidoe, it is helpful for me and i have one question about a counter, can i remove that counter if i am copping data from 1 sheet?
This is very good and it gives me an idea for data extraction. However it does not work for me and i was wondering if you could help me.
I am in need of extracting data from multiple workbooks with each workbook having a different name, from a particular worksheet (lets call it sheet 1) all in the same folder.
Very interesting, how would you do it with data that is constently added into the source files ?
tnq so much sir it is very helpful for me tnq tnq so much
I’m attempting to used this vba code, however I keep getting an “Expected: Line number or label or statement or end of statement error” when add in my files location address. Can you help?
Sub copydata()
Dim FolderPath As String, Filepath As String, Filename As String
FolderPath = “C:\mandar-test\”
Hello,
I tried using the VBA but getting “Compile Error:Syntax Error”.
Workbooks.Open Filename:=”C:\Macro_Testing1\Macro-Brazil.xlsx”
Could you please help me understanding the issue here?