Para listar os arquivos, voce usa a bilblioteca Windwos Scripting Runtime (FSO) que pode ser adicionada pelo manu ferramentas.

Para abrir, use o Workbooks.Open
Uma vez aberta, copie os dados apontando para a planilha e intervalo de origem

Worksheets("Origem").Range("A1:D1000").Copy

Cole na planilha de destino no ser arquivo de consolidação

Worksheets("Destino").Range("A1").PasteSpecial

O Caminha é mais ou menos isso

Exemplo de código que abre todos os arquivos da mesma pasta em que consta o arquivo base (ZAtualiza.xls), que deve ser o último de uma lista em ordem alfabética (pode ser de outra maneira, mas prefiro assim).

Sub Atualizar()
Dim f, fs, fc, f1

Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(ThisWorkbook.Path)
Set fc = f.Files

Application.DisplayAlerts = False

For Each f1 In fc

If f1 = ThisWorkbook.Path & "ZAtualiza.xls" Then
Application.DisplayAlerts = True
Exit Sub
Else
Workbooks.Open Filename:=f1
End If
Next

Application.DisplayAlerts = True

End Sub