Se vc clicar sobre a palavra CodeName no VBA e teclar F1, virá a ajuda para essa propriedade e também a explicação que vc deseja:

Referência sobre o desenvolvedor do Excel
Worksheet.Propriedade CodeName
Retorna o nome de código do objeto. String somente leitura.
...
O valor que você vê na célula à direita de (Nome) na janela Propriedades é o nome de código do objeto selecionado. Você pode alterar o nome de código de um objeto em tempo de design alterando este valor, mas não pode alterar esta propriedade por programação em tempo de execução.
...

Portanto é uma propriedade somente leitura então não pode ser alterada em tempo de execução. Mas pode alterar em tempo de design ali no VBE mesmo, exibindo a janela Propriedades e escolhendo uma das planilhas na janela Projeto. Lá tem dois Name's sendo que o primeiro está entre parênteses. Esse é que é o CodeName, que vc quer alterar. O segundo é o nome normal da planilha que vc também pode alterar ali, se quiser.

Sem comentário, Edson. Mas uma vez, grato!!