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

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.

Published by

Dinesh Kumar Takyar

Welcome to exceltrainingvideos.com! My aim is to help you learn MS Excel including VBA. I always felt that a good way to learn something was to find solutions to problems in that domain. That is why I share these Excel videos with you. Mostly these are questions asked during my corporate training sessions. I've been training individuals and companies in computers since 1991. I did my B. Sc. (Vordiplom), M. Sc. (Diplom) and Ph.D. (Dr. rer. nat.) from Hamburg, Germany. The best thing about solving some of my visitor's questions is that I also gain new insights. For more Excel VBA solutions you may like to visit my YouTube channel: http://youtube.com/familycomputerclub For a structured Excel VBA training course online you can visit: https://www.youtube.com/excelvbaonline

One thought on “Get Data from Multiple Sheets in Multiple Workbooks into Master Workbook with VBA”

  1. 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?

Leave a Reply

Your email address will not be published. Required fields are marked *