leo, o ideal seria anexar um modelo reduzido e compactado, se não for possivel, você pode utilizar o Gravador de Macros, realizar as ações e depois colocar o código aqui para uma analise.
Nas ações efetuadas você terá de definir um range para os dados e seria neste ponto que teriámos de trabalhar, com certeza terá uma linha assim :
Utilizei o gravador com dados da internet por não ter nenhum BD aqui, mas é mais para ilustrar, veja que Destination:=Range("$A$4") é o local onde os dados serão colocados.
With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;C:Program Files (x86)Microsoft OfficeOffice12QUERIESCotações de Ações para Investidores do MSN MoneyCentral.iqy" _
, Destination:=Range("$A$4"))

Com sua rotina poderemos procurar ajustar a sua necessidade.

[]s

Olá Mauro, obrigado pela resposta. Estou tentando desevolver alguma coisa aqui, e retorno assim que tiver algo. Só para ilustrar melhor o problema vou deixar uma imagem em Anexo.
A pessoa que fazia isso antes de mim usava CTRL C, CTRL V; porém, ao todo sao mais de 200 tabelas mensais e eu estou procurando um jeito mais inteligente de fazer isto.

leo, pelo que entendi em sua figura, os arquivos são abertos antes e depois copia e cola na relatorio, só não entendi a parte em que você diz que utiliza a "função "Dados -> Conexões", por isto pedi para anexar os arquivos e detalhar os passos que utiliza para tal operação.

[]s