[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.

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);””)

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.

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.

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

Curso Excel Completo – Do Básico ao VBA

Quer aprender Excel do Básico, passando pela Avançado e chegando no VBA? Clique na imagem abaixo: