Tente adequar esse codigo

Sub Consolidar()

Dim FSO As Object
Dim Pasta As String
Dim Planilha As Object
Dim OpenBook As String

Set FSO = CreateObject("Scripting.FileSystemObject")
Pasta = "C:" 'Pasta com as planilhas que serão abertas e copiadas

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

For Each Planilha In FSO.GetFolder(Pasta).Files

If InStr(1, Planilha, ".xlsx") = 0 Then GoTo PRÓXIMO

Workbooks.Open (Planilha)
OpenBook = ActiveWorkbook.Name

'Seu código para copiar

Windows(ThisWorkbook.Name).Activate

'Seu código para colar

Application.CutCopyMode = False
Workbooks(OpenBook).Close False
PRÓXIMO:
Next

Application.ScreenUpdating = True

MsgBox "Dados Copiados com Sucesso!", vbInformation, "Aviso"

Application.Calculation = xlCalculationAutomatic

End Sub

Muito obrigado pelo retorno.

Vou tentar adequar o código à minha necessidade e qualquer coisa eu aviso.

Obrigado mesmo!