Bom dia!

Veja se este código te ajuda:

Sub ImportarVariosArquivos()
    Dim Pasta As String
    Dim Arquivo As String
    
    'Habilita a captura de erros
    On Error GoTo ERRO
    
    'Abre caixa de diálogo para selecionar a pasta onde estão os arquivos a serem importados
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Show
        Pasta = .SelectedItems(1)
    End With
    
    'Lista o primeiro arquivo a ser importado
    Arquivo = Dir(Pasta & "" & "*.xls*")
    
    'Laço para abrir cada um dos arquivos
    Do
        'Antes de abrir verifica se o arquivo não é o próprio (macro)
        If Arquivo <> ThisWorkbook.Name Then
            
            'Abre o arquivo
            Workbooks.Open Pasta & "" & Arquivo
            
            'Copia o conteúdo para a primeira linha vazia
            ActiveSheet.Range("A2:H" & ActiveSheet.[A1].CurrentRegion.Rows.Count).Copy _
            ThisWorkbook.Sheets("Importar").Range("A" & ThisWorkbook.Sheets("Importar").[A1].CurrentRegion.Rows.Count + 1)
            
            'Fecha o arquivo
            Workbooks(Arquivo).Close False
        End If
        
        'Lista cada um dos demais arquivos da pasta
        Arquivo = Dir
    Loop While Arquivo <> ""
    Columns("A:H").AutoFit
    MsgBox "Fim de Importação dos arquivos"
    Exit Sub
ERRO:
    MsgBox "Houve o seguinte erro na importação dos arquivos: " & vbLf & vbLf _
    & "Código do Erro: " & Err.Number & vbLf & "Descrição: " & Err.Description, vbCritical, "Erro na Execução da Macro"
    Exit Sub
End Sub

Abraço

OlÁ!!
Não consigo fazer o teste porque na hora que abre a tela para eu selecionar os itens, só consigo selecionar 1.

Ahh! Entendi
Todos devem estar dentro de uma pasta específica...

Ajudou sim, mas seria interessante que eu conseguisse selecionar as planilhas que precisasse importar, pois demanda tempo ficar movendo e copiando pastas.
Pode confundir um pouco também, pois são muitas plans

Boa tarde!

Veja se este código atende:

Sub Importar()
    Dim Arquivo
    
    With Application.FileDialog(msoFileDialogFilePicker)
        .Show
        .AllowMultiSelect = True
        For Each Arquivo In .SelectedItems
            Workbooks.Open Arquivo
            
            'Copia o conteúdo para a primeira linha vazia
            ActiveSheet.Range("A2:H" & ActiveSheet.[A1].CurrentRegion.Rows.Count).Copy _
            ThisWorkbook.Sheets("Importar").Range("A" & ThisWorkbook.Sheets("Importar").[A1].CurrentRegion.Rows.Count + 1)
            
            'Fecha o arquivo
            ActiveWorkbook.Close False
        Next
    End With
    Columns("A:H").AutoFit
    MsgBox "Fim de Importação dos arquivos"
    
End Sub

Abraço

Exatamente o que eu precisava, meu caro!

Muito Obrigado!