Macro to consolidate data from different files in a folder

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.

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:

Code:

---

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

---