Isso é muito simples.
Você vai usar isso apenas uma vez? ou isso será uma rotina que vai se repetir varias vezes?
Se for repetir varias vezes crie um padrão de nomenclatura para suas planilhas.
O primeiro passo e criar uma função para abrir as planilhas, algo do tipo
Private Sub CommandButton1_Click()
Workbooks.Open ("C:\Documents and Settings\Administrador\Meus documentos\B.xls")
End Sub
Se tiver tudo na mesma pasta eu gosto mais de usar a função ThisWorkbook."sua planilha.xlsx"
Apos feito isso faça um laço para percorrer as abas uma a uma, algo do tipo
'Primeiro você seleciona a planilha que contem os dados
window("sua planilha.xlsx").activate
'depois faz um laço dentro dessa planilha
'for each ws in worksheets
plan = ws.name
sheets(plan).select
'pega a ultima celula com valor
rLast = Range("A1048576").End(xlUp).Row
'seleciona os dados a serem copiados ate rlast
'seleciona a planilha para consolidar
window("plan consolidada").activate
'pega a ultima celula com valor para saber a partir de que celula vai colar
rLast = Range("A1048576").End(xlUp).Row +1
'cola e volta para a planilha com dados
window("planilha com dados").activate
next
'repete isso para cada workbook que não tem erro.
abs