Boa tarde. Pelo que entendi você quer que na célula A1 da primeira planilha seja informado sempre o nome da quinta planilha. Tenta esse código:

Sub Exemplo1()
Sheets(1).Range("A1") = Sheets(5).Name
End Sub

Infelizmente não foi Celso, a Plan5 aparece na Macro mas o nome não aparece em A1(Plan1), eu coloquei esse comando em módulos, teria que ser na planilha

Comigo o código funcionou

Private Sub Workbook_Open()
Sheets(1).Range("A1") = Sheets(5).Name
End Sub

O codigo acima funciona se a plan5 estiver na posição 5, no entanto .u tenho a pergunta de 1000.000  

O que vc vai fazer com essa informação???
Pq vc precisa do nome da plan5 na celula???

@mprudencio 

Boa pergunta, a minha idéia era 'jogar' num banco de dados os registros de uma planilha principal os dados do mês e ano com mais de 50 linhas, mas na busca do mês no BDados não consegui devido a a amplitude.

Daí optei por uma macro que salva o mês registrado (zerando a Plan principal) em uma planilha nova, o ruím é que vai acumulando planilhas.

O primeiro registro do mês começa na Plan5 nomeada automaticamente com o nome do mês, eu conseguindo o nome da Plan5 (o mês) na Planilha principal, vai dar início a uma lista de meses para busca (validação) das planilhas anteriores.

Eu ainda prefiro um BDados, se você puder me ajudar ou dar uma dica agredeceria muito.

Primeiro excel nao é um banco de dados, logo recomenda-se nao usar para este fim.

Explica melhor sua necessidade, pq gravar todos os dados na mesma planilha alem de mais simples é o mais correto.

@dmorais 

Valeu DJunqueira, eu alterei o nome da planilha e A1 atualiza depois que fecho e abro o arquivo, mas já estaria bom demais, só que nesta pasta de trabalho eu tenho esse comando abaixo e deu conflito. tem como ajustar os dois comandos?

Private Sub Workbook_Open()
Sheets(1).Select
End Sub

@mprudencio 

Refiro-me a uma planilha como BDados sendo alimentado por uma macro 

Isso q vc está querendo fazer seria melhor administrado pelo Power Query.

 Faz um esforço de montar uma planilha exemplo sem dados sensíveis q uma solução poderá ser apresentada.

Private Sub Workbook_Open()
Sheets(1).Range("A1") = Sheets(5).Name
'        Questão de ordem
Sheets(1).Select
End Sub

Postado por: @mprudencio

Primeiro excel nao é um banco de dados, logo recomenda-se nao usar para este fim. 

Explica melhor sua necessidade, pq gravar todos os dados na mesma planilha alem de mais simples é o mais correto.

Tendo a concordar, mas o Excel suporta bem até uns 50.000 dados, tecnicamente ultrapassa 1 milhão, mas se vc tem uma entrada de dados tão constante q chega a 1 milhão em pouco espaço de tempo certamente o Excel não é a melhor opção.

Se vc disponibilizar um modelo da sua planilha fica mais facil ajudar