How to use Excel Vba macro to import data from multiple workbooks to a Main workbook

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.

https://www.ozgrid.com/forum/forum/help-forums/excel-vba-macros/1204102-excel-vba-macro-to-import-data-from-multiple-workbooks-to-a-main-workbook

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.