Ao invés de exibir uma tela de erro para a pessoa que está usando a planilha, interessante é usar o On Error Go To para tratarmos o erro.

Ou seja no lugar do erro classico na tela,

On Error GoTo

irá exibir uma mensagem personalizada.

On Error GoTo 2

O código abaixo (e/ou em anexo) está o código com erro tratado
_________________________________________

Sub separa()

Dim linha, nome, prenome, sobrenome, espaco, tlin
Dim letras, aviso
'declara variaveis

On Error GoTo aviso
'tendo erros, pula para aviso

tlin = WorksheetFunction.CountA(Columns("A"))
'conta quantas linhas na coluna a

For linha = 2 To tlin
   nome = Cells(linha, "a")
   espaco = InStr(1, nome, " ") 'localiza o espaço
   prenome = Left(nome, espaco)
   letras = Len(nome) 'conta caracteres
   sobrenome = Right(nome, letras - espaco)
   Cells(linha, "b") = prenome
   'alterei o codigo , no lugar de linha coloquei o nome
   'pra mostrar o tratamento do erro
   Cells(nome, "c") = sobrenome
Next

Exit Sub
'se a macro estiver ok, acaba aqui

aviso: MsgBox "A macro está com problemas! ligue 190"

End Sub

_________________________________________