Vocês votaram na enquete aqui do site excel e vba e em segundo lugar ficou o assunto VBA Copiar colar arquivos diferentes.
A maneira que demonstro é uma mais avançada e também com menos código.
Porém se você não gostar ou achar complicado aqui mesmo no site há outras maneiras (passo1 e passo2)que envolvem copiar célula por célula com um loop.
Você que escolhe a melhor maneira para trabalhar excel com vba.
1 – Vamos ao codigo VBA Copiar Colar arquivos diferentes:
________________________________________________________
Sub COPIAR_COLAR_ARQUIVO_DIFERENTES()
'Declarações
Dim appExcel As Excel.Application
Dim wb As Excel.Workbook
'A Aplicação Excel é criada aqui:
Set appExcel = CreateObject("Excel.Application")
'A linha abaixo é importante: eu estou dizendo que todo o processo seja invisível.
'Talvez para testes seja importante deixar como True e assim acompanhar o processo
appExcel.Visible = False
'Note que, na linha abaixo, foi adicionado um Workbook (um plan)
'E esse workboook fica nomeado como "wb"
Set wb = appExcel.Workbooks.Add
'Agora para simplificar o processo, também vou gravar o nome da planilha origem
Set oldPlan = Workbooks("planilha_antiga.xlsm").Worksheets("Plan1")
'Então copio todos os dados da tabela
oldPlan.Range("A2").CurrentRegion.Copy
'Na linha abaixo eu colo os dados na nova planilha (lembre-se do "wb")
wb.Worksheets("Plan1").Range("A1").PasteSpecial xlPasteValues
'Nas linhas abaixo eu salvo a planilha no mesmo diretorio que a planilha antiga
diretorio = ThisWorkbook.Path & "\"
nome = "exemplo.xls"
appExcel.DisplayAlerts = False
wb.SaveAs Filename:=diretorio & nome, FileFormat:=xlNormal
'Agora eu elimino o processo aberto do excel
appExcel.Quit
'E finalizado as declarações
Set wb = Nothing
Set appExcel = Nothing
End Sub
2 – Vídeo Demonstrativo