How to use Excel Vba macro to import data from multiple workbooks to a main workbook
Category: [Excel] Demo Available
How to use Excel Vba macro to import data from multiple workbooks to a main workbook
Requirement:
A workbook has been created of a continuing list of data (fixed).
Multiple persons need to update the list at the same time. Sharing the file is not an option because it becomes to slow.
One way to alleviate this problem is to create copies from this file and have multiple persons work on their own file. Then in the masterfile there would be a button which runs a macro. The macro needs to loop through the copied files, find what files have been updated. If a copied file is updated, it needs to copy the data and add this to the list in the masterfile.
Solution:
Code:
Sub CopyData()
Application.ScreenUpdating = False
Application.EnableEvents = False
Dim desWs As Worksheet, scrWs As Worksheet, scrWB As Workbook
Set desWs = ThisWorkbook.Sheets("Invoices")
Const strPath As String = "T:\invoice\"
ChDir strPath
strExtension = Dir(strPath & "*.xlsm")
On Error GoTo errHandler
Do While strExtension <> ""
If strExtension <> ThisWorkbook.Name Then
Set scrWB = Workbooks.Open(strPath & strExtension)
Set scrWs = scrWB.Sheets("Invoices")
If scrWs.Range("A2") <> "" Then
scrWs.UsedRange.Offset(1, 0).Copy desWs.Cells(desWs.Rows.Count, "A").End(xlUp).Offset(1, 0)
scrWs.UsedRange.Offset(1, 0).ClearContents
End If
scrWB.Close True
End If
strExtension = Dir
Loop
errHandler:
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
Obtained from the OzGrid Help Forum.
Solution provided by Mumps.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to use IF/Then in VBA code |
| How to use VBA code for multiple IF conditions |
| How to use VBA code to convert date format |
| How to use VBA code to auto generate invoice number |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.