Deixe a disposição sua planilha para vermos melhor...
veja se não tem mais de duas planilhas abertas ou o nome da planilha em negrito selecionado..

Private Sub CommandButton1_Click()

Dim W As Worksheet
Dim UltCel As Range

Sheets("Somatória").select

Range("A6").Select
ActiveCell.Value = Application.WorksheetFunction.Sum(Worksheets("Somatória").Range("A1:A5"))

Set W = Sheets("Somatória") ( erro em tempo de execução 9) justamente neste comando que o erro ocorre.

Por acaso na mensagem de erro aparece "Subscrito fora do intervalo" ?

Se sim, verifique se existe no arquivo planilha com aquele nome escrito exatamente igual ao nome que está no código.

So complementando o que o Osvaldo disse:

Exatamente nesse caso é se tudo maiusculo, no codigo tem estar maicusculo, se tiver acento no codigo tem que ter acento.

Todas essas Opções são diferentes:

SOMATORIA
SOMATÓRIA
somatoria
somatória
Somatoria
Somatória

Aparece sim a mensagem Osvaldomp

Aparece sim a mensagem Osvaldomp

E ... ?

Aparece sim a mensagem Osvaldomp

E ... ?

A resposta ele já deu no post dele :

Por acaso na mensagem de erro aparece "Subscrito fora do intervalo" ?

Se SIM, verifique se existe no arquivo planilha com aquele nome escrito exatamente igual ao nome que está no código.

No código está referenciado a aba de nome Sheets("Somatória").Select, e a mensagem está dizendo que ela não existe, se existe verifique o nome, pode ser que esteja sem o acento do "ó".

Marcelo, neste caso a rotina não faz distinção de maiúsculas, o erro só pode ser por não existir ou o acento mesmo.

[]s

Mauro ja tive diversos problemas justamente por esta diferença

Se a Aba estiver Marcelo e no VBA colocar Set Sheets("MARCELO"), da erro...

Pelo menos no 2010 32 bits que uso acontece sempre

... a rotina não faz distinção de maiúsculas, o erro só pode ser por não existir ou o acento mesmo.

Verdade.
Os problemas mais comuns que tenho visto em relação aos nomes de planilhas são os referentes a acentos, ao c e ç e espaço antes ou depois do nome.

Com maiúsculas/minúsculas não.

No presente caso o código aceitaria nomes como Somatória, sOmAtóRiA, sOMATÓria, SOMATória, entre outros.

Boa noite pessoal, descobri o erro, erra só substituir o nome somatória que tinha lá por plan1, eu não tinha observado que na planilha que olhei como exemplo o cara tinha mudado o nome, quando coloquei plan1 de certo.