Excel Copiar e colar resultados de fórmulas ao digitar VBA

[Excel] Copiar e colar resultados de fórmulas ao digitar VBA

É mais comum do que se imagina a necessidade de copiar e colar resultados de fórmulas ao digitar. Por exemplo, com VBA, podemos fazer com que ao digitar o código de um produto sejam retornados o seu nome e o preço naquele momento.

Este é um caso comum por exemplo em planilhas aonde digitamos um orçamento ou ainda vendas, movimentações de estoque, movimentações financeiras com cotação, entre várias situações.

Mas não é só isso, no exemplo que veremos poderá usar qualquer fórmula que ao você digitar e alterar um valor, por exemplo, a fórmula será calculada e o resultado será colado, “matando” assim a fórmula e ficando apenas o valor fixo no Excel.

Copiando e colando fórmulas como texto automaticamente

No exemplo abaixo temos uma lista de vendas aonde são digitados os dados de vendedor, pedido, cliente, número do item.

Copiar e colar resultados de fórmulas automaticamente Excel VBA

A descrição do item e o valor são retornados automaticamente utilizando fórmulas.

Gostaríamos que ao digitar o campo Item, que se refere ao código do produto, fossem não só retornados a descrição e o valor do item, como também fossem colados somente os resultados das fórmulas naquele momento.

Isso se deve ao fato de que os valores podem mudar e queremos o valor naquele momento, no momento em que digitamos a venda.

Para isso o primeiro passo é simplesmente fazer as fórmulas que precisa em cada um dos campos.

Como pode ver a fórmula utilizada foi um Procv em uma tabela:

=SEERRO(PROCV(E8;tItens[#Tudo];2;0);””)

Fizemos então a fórmula para o valor também, mas poderia ser feito para quaisquer campos e com qualquer fórmula.

=SEERRO(PROCV(E8;tItens[#Tudo];3;0);””)

[Excel] Copiar e colar resultados de fórmulas automaticamente 1

Com a planilha com as fórmulas preparadas nós já podemos partir para a automação de copiar e colar os valores automaticamente do resultado das fórmulas no Excel.

Copiando e colando valores de fórmulas no Excel ao digitar

Agora iremos fazer com que ao digitar o valor o resultado da fórmula já seja copiado e colado automaticamente.

Para isso você deve habilitar o VBA no seu Excel, veja como em Habilitar Guia Desenvolvedor.

Na guia Desenvolvedor clique no botão Gravar Macro.

Clique sobre a fórmula com a célula e pressione F2 para editar a célula e tecle ENTER, sem alterar a fórmula.

Repita a operação para a célula ao lado, que tem a fórmula que retorna o valor.

Isso fará com que o Excel grave as operações realizadas e fiquem disponibilizadas em um módulo no VBA aonde poderá utilizar este código para o seu processo.

Este método funcionará para qualquer fórmula.

Usando a fórmula gravada pela macro para o copiar e colar resultado de fórmulas no Excel

Com os dados gravados das fórmulas criamos então um procedimento no VBA que chamamos de lsAtualizarValores, conforme imagem abaixo.

[Excel] Copiar e colar resultados de fórmulas automaticamente 3

Entenda as partes conforme as cores:

Roxo: A planilha que mudamos o nome internamente como Cadastro está recebendo na sua coluna F & lLinha (Linha é um parâmetro que é passado com o número da linha) e utilizamos então a propriedade FormulaR1C1 que irá receber a fórmula que gravamos via macro (em vermelho).

Vermelho: As fórmulas são passadas conforme cada uma das colunas desejadas.

Verde: São então copiados na primeira linha da parte verde os dados das células alteradas das colunas F e G e em seguida são colados como valores utilizando PasteSpecial xlPasteValues. E na linha seguinte é limpa a memória dos dados que estão sendo copiados com o CutCopyMode = False.

Azul: Selecionamos a célula da coluna E da linha seguinte a alterada, para dar o mesmo efeito de dar um Enter no Excel.

Abaixo o código VBA para copiar e colar resultados de fórmulas.

Sub lsAtualizarValores(ByVal lLinha As Long)
    Cadastro.Range("F" & lLinha).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-1],tItens[#All],2,0),"""")"
    Cadastro.Range("G" & lLinha).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-2],tItens[#All],3,0),"""")"
    
    Cadastro.Range("F" & lLinha & ":" & "G" & lLinha).Copy
    Cadastro.Range("F" & lLinha & ":" & "G" & lLinha).PasteSpecial xlPasteValues
    
    Application.CutCopyMode = False
    
    Cadastro.Range("E" & lLinha + 1).Select
End Sub

Chamando o código para copiar e colar resultados de fórmulas no Excel com VBA

Por fim temos que realizar a chamada do código no momento em que for digitado o código do produto.

Realizando assim o cálculo das fórmulas e colando somente os resultados nas células das colunas F e G correspondentes.

[Excel] Copiar e colar resultados de fórmulas automaticamente 4

Para isso faça o seguinte procedimento:

  1. No VBE clique duas vezes sobre a planilha que terá a aplicação das fórmulas, no nosso caso a planilha Cadastro.
  2. Selecione o objeto Worksheet e o evento Change nas opções de topo, será criado então o evento conforme o item circulado em vermelho.
  3. Em roxo uma parte muito importante, é aonde desligamos o evento, Application.EnableEvents = False no VBA e aonde desligamos também a atualização de tela no VBA, Application.ScreenUpdating = False. Estas propriedades do Excel tem que ser ligadas novamente no final.
  4. E por fim, verificamos se a coluna alterada é a coluna 5, ou seja, se a coluna alterada foi a coluna do valor, e se for, chamamos a lsAtualizarValores passando para ela o número da linha que está sendo alterada.

Esta alteração fará então com que ao alterar o valor de qualquer célula da coluna 5 (E), seja então chamado o procedimento que criamos lsAtualizarValores, que com o número da linha alterada realiza então o processo de copiar e colar o resultado das fórmulas n o Excel automaticamente com VBA.

Download da planilha

No topo deste artigo há uma vídeo-aula aonde você aprende passo-a-passo como realizar copiar e colar resultados de fórmulas no Excel automaticamente, assim como descrevi neste tutorial.

E você também pode realizar o download da planilha com o exemplo pronto no link abaixo.

Basta clicar no botão e digitar seu nome e e-mail para fazer parte da nossa newsletter gratuita aonde enviamos novidades sobre Excel e também ofertas de nossas planilhas prontas e cursos.

Baixe a planilha