Macro to consolidate data from different files in a folder

[macroidiot22]

Hi
I have a template with multiple sheets. One of the sheet is 'consolidate return with macro' sheet where i would like to consolidate data from the different file in the folder. Hence the macro must do the following:
1. ask user to choose the folder where all the files - Annex A1 (can be different names) are
2. Consolidate all the data from Annex A1 to the main template - consolidate return with macro sheet
3. Message to inform user importing is completed.

Can a macro do all these? If yes, pl share the code. Hv been trying to write from scratch from going nowhere. Thanks.

[georgiboy]

Welcome to the forum macroidiot22,

Maybe you could give the below a try as a start, select the folder where the files reside when asked:

Sub Test()    
    Dim sFolder As String, myExtension As String, wsAnnex As Worksheet, wb As Workbook
    Dim var As Variant, tWb As Workbook, tWs As Worksheet, myFile As String


    myExtension = "*.xlsx"
    Set tWb = ThisWorkbook
    Set tWs = tWb.Sheets("Consolidated Return with Macro")


    With Application.FileDialog(msoFileDialogFolderPicker)
        If .Show = -1 Then
            sFolder = .SelectedItems(1) & "\"
        End If
    End With
    
    If sFolder <> "" Then
        myFile = Dir(sFolder & myExtension)
        
        Do While myFile <> ""
            Set wb = Workbooks.Open(Filename:=sFolder & myFile)
            DoEvents
            On Error GoTo ErrHand
                Set wsAnnex = wb.Sheets("Annex A1")
            On Error GoTo 0
            With wsAnnex
                var = Array(.Range("C2").Value, .Range("B10").Value, .Range("B14").Value, .Range("B15").Value, .Range("B16").Value)
            End With
            tWs.Range("A" & tWs.Range("A" & Rows.Count).End(xlUp).Row + 1).Resize(1, 5) = var
            wb.Close False
            myFile = Dir
        Loop
    End If
    
    Exit Sub
ErrHand:
    MsgBox "Workbook without the right tab name found"
End Sub