Combining multiple files into one master file

[Austen G]

Hi,

So I am trying to put together a peer review checklist that will be used to document errors found in newly developed software. There will be multiple reviewers who will all use the same template to go through and do independent code reviews. These review files will then all be saved into the respective folder for the system software review.

Example Path: "C:\users\JohnDoe\TestEngineering\System123\CodeReview..".

I would then like for all of the issues found in each review to be placed into a master document that has the ability to update if something is changed in one of the source documents. Here is an example of the Peer Review Template:

Thanks in advance!

-AG

 

[kanadaaa]

Try this in a standard module:

Sub LoopAllExcelFilesInFolder()
    Dim wb As Workbook, myPath As String, myFile As String, myExtension As String, FldrPicker As FileDialog, srcLr As Long, desLr As Long, Done As Boolean

    'Optimize Macro Speed
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual

    Done = False

    'Retrieve Target Folder Path From User
    Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)

    With FldrPicker
        .Title = "Select A Target Folder"
        .AllowMultiSelect = False
        If .Show <> -1 Then GoTo NextCode
        myPath = .SelectedItems(1) & "\"
    End With

    'If Canceled
NextCode:
    myPath = myPath
    If myPath = "" Then GoTo ResetSettings

    'Loop through each Excel file in folder    
    myExtension = "*.xls*"
    myFile = Dir(myPath & myExtension)
    Do While myFile <> ""
        Set wb = Workbooks.Open(Filename:=myPath & myFile)
        DoEvents
        srcLr = wb.Worksheets(1).Range("A" & Rows.Count).End(xlUp).Row
        With ThisWorkbook.Worksheets(1)
            desLr = .Range("A" & Rows.Count).End(xlUp).Row + 1
            .Cells(desLr, "A").Resize(srcLr - 9, 5).Value = wb.Worksheets(1).Cells(10, "A").Resize(srcLr - 9, 5).Value
        End With
        wb.Close SaveChanges:=True
        DoEvents
        myFile = Dir
    Loop
    Done = True

ResetSettings:
    'Reset Macro Optimization Settings
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    If Done Then
        MsgBox "Task Complete"
    Else
        MsgBox "Canceled"
    End If

End Sub