Eu particularmente prefiro utilizar a função Texto para colunas, muito mais simples e direta.
Mas na rotina acima experimente na ultima linha a seguinte alteração

....
.Value = .Value*1
...

Nao funcionou deu erro qndo rodei a macro.

preciso que essa coluna seja números para conseguir calcular outros dados em outras planilhas, peguei ela assim já nao sei o que teria que fazer pra alterar tudo mas todos os dias que lanço os dados tenho que ficar alterando isso e limpando a formatação dos dados que tambem nao sei fazer por VBA e vou procurar depois de resolver esse problema o que torna chato e gera perda de tempo.

Qual erro gerado???

Erro em tempo de execucao 13 tipos incompativeis

Provavelmente ele está identificando algum texto e numero incorretamente, o melhor seria você executar o código em modo Debug, ou se for o caso, verificar se o valor que ele esta trazendo é realmente um numero armazenado como texto, porque se for o caso era para funcionar.
Eu utilizo o mesmo que o Reinaldo mencionou, que seria o ".Value = .Value*1 ou .Value /1", mas seria interessante você postar o seu modelo para que possamos estar analizando.

Posta a planilha para melhor identificarmos a melhor solução....

Visite meu blog:

programacaopassoapasso.wordpress.com

Após executar a macro só indo na celular e dando um F2+enter a planilha fica como eu gostaria.. amanha tentarei upa-la.

E para limpar a formatação de celulas como faco isso com VBA?

Segue.

Preciso que a planilha "DADOS" tenha A coluna I com valores numéricos.

Uma dúvida, você utilizou o gravador de macro para criar os códigos utilizados não?
Se for o caso, descreve a sua nescessidade ae que tento te dar uma mão e criar uma forma mais rápida e agil de automatizar isso

Sim, usei sim ;/ nao manjo de programação de VBA

Acho que se trata de uma programação simples:

Eu vou colar informações na planilha "DADOS" eu preciso limpar a formatação dela pois se não o arquivo fica com por volta de 60Mb conforme outro post que abri aqui, organizar por data, depois eu preciso trocar as informações da coluna "I" (tirar o ponto e colocar a virgula e transformar em modo numero)

E finalmente Recortar e colar cada MPxxx na planilha correspondente antes de colar eu coloquei para limpar a planilha.

1- vou colar os dados
2- limpo a formatacao (posso fazer isso manual)
3- troco o ponto da coluna "I" por virgula e torno ela um numero
4- limpar todas as planilhas que comecam com "MP***"
5- colar cada uma com sua correspondência(COLUNAS A até M)

é bastante coisa, mas se conseguir agradeço.

Fiz as macros separadamente (gravada), copiei o código e colei no anterior. Isso pode gerar problemas?

Todos os números da coluna "I" ficam com a notificação verde para converter do formato texto para numero, porem se eu selecionar a celula e der enter o problema some e consigo calcular normalmente.

Alguém sabe pq acontece isso? Parece que o excel precisa "abrir" a celular para "atualiza-la"

Unica "solução" que encontrei para fazer isso via macro foi copiar a coluna "I" colar em qualquer outro lugar, Multiplicar tudo por 1 e copiar a coluna colocando de volta na coluna "I"

Olá...

Para trocar o ponto pela virgula tente essa macro aqui:

Sub TROCAR_PONTO_POR_VIRGULA()
Dim LIN As Integer
LIN = 2

Plan1.Select

Do Until Plan1.Range("A" & LIN).Value = ""
Plan1.Range("I" & LIN).NumberFormat = "0.00"
Plan1.Range("I" & LIN).Value = Plan1.Range("I" & LIN).Value
LIN = LIN + 1
Loop

End Sub

As demais vou ver se consigo preparar aqui e posto assim que pronto

Visite meu blog:
programacaopassoapasso.wordpress.com

Para apagar as informações das planilhas que começam com "MP" use essa macro:

Sub Apagar_Planilhas_Inicio_MP()

Dim i, IndicePlanilha As Integer
Dim LIN As Integer

IndicePlanilha = Sheets.Count

For i = 1 To IndicePlanilha

If Sheets(i).Name Like "MP" & "*" = True Then

LIN = 2

Do Until Sheets(i).Cells(LIN, 1).Value = ""
LIN = LIN + 1
Loop

Sheets(i).Range("A2:P" & LIN).ClearContents
End If

Next i

End Sub

E PARA A ULTIMA QUESTÃO DE COPIAR OS DADOS PARA AS RESPECTIVAS PLANILHAS USE ESSA MACRO:

Sub Colar_Informacoes_Correspondentes()

Dim i, IndicePlanilha As Integer
Dim LIN As Integer, LINHA As Integer

LINHA = 2
LIN = 2

IndicePlanilha = Sheets.Count

For i = 1 To IndicePlanilha

Do Until Plan1.Range("A" & LIN).Value = ""

If Sheets(i).Name Like "MP" & "*" = False Then Exit Do

If Sheets(i).Name = Plan1.Range("B" & LIN).Value Then
Sheets(i).Range("A" & LINHA).Value = Plan1.Range("A" & LIN).Value
Sheets(i).Range("B" & LINHA).Value = Plan1.Range("B" & LIN).Value
Sheets(i).Range("C" & LINHA).Value = Plan1.Range("C" & LIN).Value
Sheets(i).Range("D" & LINHA).Value = Plan1.Range("D" & LIN).Value
Sheets(i).Range("E" & LINHA).Value = Plan1.Range("E" & LIN).Value
Sheets(i).Range("F" & LINHA).Value = Plan1.Range("F" & LIN).Value
Sheets(i).Range("G" & LINHA).Value = Plan1.Range("G" & LIN).Value
Sheets(i).Range("H" & LINHA).Value = Plan1.Range("H" & LIN).Value
Sheets(i).Range("I" & LINHA).Value = Plan1.Range("I" & LIN).Value
Sheets(i).Range("J" & LINHA).Value = Plan1.Range("J" & LIN).Value
Sheets(i).Range("K" & LINHA).Value = Plan1.Range("K" & LIN).Value
Sheets(i).Range("L" & LINHA).Value = Plan1.Range("L" & LIN).Value
Sheets(i).Range("M" & LINHA).Value = Plan1.Range("M" & LIN).Value

LINHA = LINHA + 1
End If
LIN = LIN + 1
Loop

LINHA = 2
LIN = 2
Next i

End Sub