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