Segue minha sugestao. Hab. a ref. "Microsoft Activex Data Objects Library"

'habilitar referencia p/ Microsoft Activex Data Objects Library
Sub ImportarDadosdeOutraPlanilhaFechada()

    Dim strDB As String
    Dim strSQL As String
    Dim adoRecSet As New ADODB.Recordset
    Dim connDB As New ADODB.Connection

    strDB = ThisWorkbook.Path & "" & "Pasta2.xlsx"   'altere de acordo c/ o local e nome da sua pasta de trabalho

    connDB.Open ConnectionString:="Provider = Microsoft.ACE.OLEDB.12.0; data source=" & strDB _
                                  & ";Extended Properties=;" & "Excel 12.0 Xml;HDR=YES;"

    strSQL = "SELECT * FROM [Plan1$]" 'altere aqui de acordo com o nome da sua planilha(aba), mantendo o($)
    adoRecSet.Open Source:=strSQL, ActiveConnection:=connDB, CursorType:=adOpenDynamic, LockType:=adLockOptimistic

    ActiveSheet.Range("A1").CopyFromRecordset adoRecSet

End Sub

Entendi boa parte, mas ainda fiquei com uma dúvida!

Como integrar por exemplo um WHILE que retira 20 dados da coluna A da plan1?

Veja se é isso que está querendo:

Sub ImportarDadosdeOutraPlanilhaFechadaViaAdoComWhile()

    Dim strDB As String
    Dim strSQL As String
    Dim adoRecSet As New ADODB.Recordset
    Dim connDB As New ADODB.Connection

    strDB = ThisWorkbook.Path & "" & "Pasta2.xlsx"   'altere de acordo c/ o local e nome da sua pasta de trabalho

    connDB.Open ConnectionString:="Provider = Microsoft.ACE.OLEDB.12.0; data source=" & strDB _
                                  & ";Extended Properties=;" & "Excel 12.0 Xml;HDR=YES;"

    strSQL = "SELECT * FROM [Plan1$] " 'altere aqui de acordo com o nome da sua planilha(aba), mantendo o($)
    adoRecSet.Open Source:=strSQL, ActiveConnection:=connDB, CursorType:=adOpenDynamic, LockType:=adLockOptimistic
    i = 2
While Not adoRecSet.EOF
Worksheets("Nome da Planilha(aba) que receberá os dados").Range("A" & i) = adoRecSet![Nome Do Campo da (coluna A) da Pasta2.xlsx] ' . (Set esta..
                                                                                                                 '  linha de acordo com seus dados)
'Worksheets("Plan1").Range("B" & i) = adoRecSet![Nome Do Campo da coluna b]
'Worksheets("Plan1").Range("c" & i) = adoRecSet![Nome Do Campo da coluna c]
adoRecSet.MoveNext
i = i + 1
Wend
adoRecSet.Close
Set adoRecSet = Nothing
End Sub

Vou testar e volto com o resultado.

Ahhh... me lembrei!

O relatório que estou montando deve funcionar da seguinte forma.

O relatório será enviado para várias outras pessoas e logicamente será aberto em PCs diferentes, desta forma este código acima irá funcionar para todos?

Lembrando que a Pasta de Trabalho1 será o relatório e a Pasta de Trabalho2 será o banco de dados.

Devem perguntar por que não faço tudo na mesma Pasta de trabalho?...

É porque somente algumas pessoas terão acesso para ver os resultados compilados retirados da Pasta de Trabalho2, assim não queria fazer um único arquivo pois se juntar todos os dados em somente uma Pasta iria passar de 10MB.

Sim o codigo ira funcionar para todos relatorios, desde que voce altere em cada um o local do banco de dados (Pasta de Trabalho2) na respectiva linha do codigo:
por ex.

strDB = "C:UsersadminarquivosPasta2.xlsx"   'altere de acordo c/ o local e nome da sua pasta de trabalho(banco de dados)

E tem mais, com esse codigo, (com pequenas alterações), e dependendo da localização fisica dos computadores, voce pode colocar sua Pasta de Trabalho2, nas nuvens e visualizar seus relatórios com a conexão via internet, de qq lugar.