COLAR VALORES SOMENTE EM CÉLULAS FILTRADAS (OU VISÍVEIS)

Gostaria de compartilhar algo que uso a anos, mas pelo que vejo no dia a dia, acredito que poucos saibam. Imagine o cenário:  você está com uma tabela de dados filtrada, aí precisa copiar e colar os dados filtrados ou visíveis para outra coluna desta mesma tabela filtrada, mas só quer fazer isso nas linhas filtradas, claro. Ou seja, você quer copiar os dados visíveis, e colar apenas nas linhas que também estão visíveis, e ainda por cima quer que o Excel preserve os dados que estão nas linhas ocultas ou “não estão no filtro”. Vou te ensinar duas formas de fazer isso: Uma com um truque usando recursos do próprio Excel e outra usando uma macro, que você verá mais à frente a utilidade e pode servir se você quiser fazer alguma automatização na sua planilha.

Vamos a uma breve introdução, para que você entenda do que estou falando:

Esta é a nossa tabela de dados, sem filtros:

Filtramos apenas pela coluna C (“Em estoque” = Sim):

E agora, imagine que a por algum motivo, eu preciso colar os dados da coluna D, que eu consegui através de um PROCV que eu “puxei” de outra planilha, para a coluna “B”, por exemplo. E quero que o “Valor/Kg” seja colocado apenas para os produtos que estão em estoque, ou seja, apenas para os que estão aparecendo no filtro.

Você pensa: “OK, vou apenas dar o velho e bom Ctrl+C na coluna D e um  Ctrl+V  na B, e pronto”. Se você tentar fazer desta forma, vai acontecer isso…

     

Isso acontece porque o Excel, ao copiar e colar considera as linhas ocultas como parte do intervalo a ser colado. Eu sempre desejei que o Excel pudesse ser inteligente e já fazer isso por padrão, já que estamos trabalhando em uma tabela filtrada mas enfim…

Agora entendeu o problema né? Isso é fácil de resolver “na unha”, copiando célula por célula se for uma tabela pequena, mas e se forem milhares de linhas? Vamos adiante!

Parte 1: Truque usando recursos do Excel:

Este pequeno truque vai atender a maioria das necessidades e você não precisará recorrer a macro.

Vamos usar a mesma planilha de exemplo, com os mesmos filtros. Para conseguir a “cola mágica” siga estes passos:

1. Já com o filtro aplicado, selecione o intervalo que você quer preencher com os dados que serão copiados:

2. Pressione a tecla F5 no teclado, na janela que aparece clique em “Especial…”

3. Clique em “Somente células visíveis” e dê OK:

4. A seleção vai ficar assim (vai aparecer a primeira célula da seleção com bordas verdes finas e o restante em cinza:

5. Agora, SEM CLICAR EM NADA, digite no seu teclado a fórmula: =D2 (porque a minha primeira linha filtrada é a 2, mas poderia ser qualquer outra, preste atenção nisso senão não vai dar certo!.

6. Pressione simultaneamente Ctrl + ENTER e veja o que acontece:

Sensacional, não é? O Excel colocou a fórmula apenas no intervalo visível. Vamos tirar o filtro para ver o que o Excel fez:

Repare que o Excel preservou as linhas que continham a interrogação. Agora é só limpar o filtro, depois copiar e colar especial para remover as fórmulas e pronto. Se isto resolveu para você, pode parar por aqui. Foi um prazer ajudar! 😉

Agora se você não pode copiar/colar especial por algum motivo, como por exemplo para preservar outras fórmulas, dados, ou se simplesmente quer fazer isso sem tirar o filtro, ou se você só gosta de macros, vamos ter que partir para a solução com… macros.

Veja o que acontece quando tentamos copiar e colar especial nas células filtradas que acabamos de aplicar nosso truque:

Logo após preencher com Ctrl+Enter o intervalo, conforme explicado mais acima, seu instinto manda você copiar e colar especial para o mesmo intervalo, para não ter que mexer com os dados que não estão filtrados (ou ocultos, como preferir), certo?

Selecionando e copiando os dados que acabaram de ser preenchidos com a “cola mágica” que explicamos mais acima…

Agora vamos selecionar B2 ou B2:B12, clicar com o botão direito e tentar colar especial, para tirar as fórmulas…

Que pena! O Excel será mais uma vez amável com você e dirá isso:

E se tentar de outro jeito, dirá isso:

E se reclamar, tomará mais isso:

Mas isso tem solução, agora você terá que recorrer a uma macro. Infelizmente não tem outra forma. Confesso que fiquei muito tempo pesquisando para encontrar uma solução confiável, que funcionasse todas as vezes em qualquer situação e que eu pudesse ter certeza que não iria destruir meus dados. Isto porque por padrão, sempre que o Excel executa uma macro, ele apaga o cache de mudanças recentes, ou seja, ao executar uma macro, você não consegue mais clicar no botão desfazer, afinal, não tem como “desexecutar” (sic) uma macro ou fazer ela executar ao contrário.

Parte 2:  Macro para copiar e colar somente em células visíveis / filtradas

Minha macro foi retirada neste site. Fiz algumas adaptações e traduzi para o português. Com ela o trabalho é executado com perfeição.

Não se preocupe, vamos passo a passo fazer isso e tenho certeza que você conseguirá. Existe uma forma de deixar isso disponível no seu Excel como uma função permanente, para isso terá que construir um arquivo .xlam, mas não vamos abordar isso aqui. Você pode ler sobre isso aqui e também aqui.

A ideia é apenas executar a macro, conferir os dados e depois removê-la, para não ter que salvar a planilha com macros.

Não se assuste com o código que vem a seguir, você não precisará alterar nada.

Siga os passos:

1. Com o Excel aberto, pressione Alt+F11, vai abrir essa tela feia, meio anos 90. É o editor do Visual Basic, onde iremos colar nosso código:

2. Agora clique com O BOTÃO DIREITO em qualquer item desta lista de objetos que aparecem na esquerda, e escolha a opção “inserir/módulo”:

3. Agora vai aparecer uma tela em branco:

4. Selecione e copie todo o código abaixo (não deixe faltar nem uma parte):

Sub CopiarColarEmCelulasVisiveis()

'Copia e cola valores em um intervalo filtrado, preservando os dados das linhas ocultas. _
(Não funciona com colunas ocultas, somente linhas ocultas.

'----------------------------------------------------------------------------------------
'Código original:
'https://www.mrexcel.com/forum/excel-questions/85288-paste-visible-cells-only-8.html
'adaptado por Cristiano Ash
'https://cristianoash.wordpress.com/
'----------------------------------------------------------------------------------------
Dim SrcRng As Range
Dim SrcRngVisible As Range
Dim DestRng As Range
Dim SrcCell As Variant
Dim DestCell As Variant
Dim DestRngColumnCount As Long
Dim DestRngRowCount As Long
Dim msgAnswer As Variant

On Error Resume Next
'Seleciona o intervalo de origem (os dados que você quer copiar)...
Set SrcRng = Application.InputBox("Selecione o intervalo de origem...", Default:=Selection.Address, Type:=8)
On Error GoTo 0
If SrcRng Is Nothing Then Exit Sub

On Error Resume Next
'Seleciona o intervalor de destino...
Set DestRng = Application.InputBox("Selecione o intervalo de destino...", Type:=8)
On Error GoTo 0
If DestRng Is Nothing Then Exit Sub

'Observe que você não pode colocar ScreenUpdating, até que o valor das input boxes _
seja definido, o Application.ScreenUpdating = False deve vir depois _
que você tiver selecionado o intervalo ou então dará um erro.
Application.ScreenUpdating = False

'Calcula a quantidade de colunas, linhas e a primeira coluna...
SrcRngRowCount = SrcRng.Rows.Count
SrcRngColumnCount = SrcRng.Columns.Count
DestRngColumnCount = DestRng.Columns.Count
DestRngRowCount = DestRng.Rows.Count
DestRngFirstColumn = DestRng.Cells(1, 1).Column

'DEtermina se os intervalos de origem e destino tem o mesmo tamanho...
If SrcRngColumnCount <> DestRngColumnCount Or SrcRngRowCount <> DestRngRowCount Then
    msgAnswer = MsgBox("Os intervalos de origem e destino não têm o mesmo tamanho." & vbCrLf & vbCrLf & _
        "Selecione os intervalos novamente e tente outra vez.", vbExclamation + vbOKOnly, "Intervalo Selecionado Inválido")
    Application.ScreenUpdating = True
    Exit Sub
End If

'Seta o intervalo de origem para apenas células visíveis
Set SrcRngVisible = SrcRng.SpecialCells(xlCellTypeVisible)

'Faz o loop por cada célula visível do intervalo de origem...
For Each SrcCell In SrcRngVisible
    'Faz o loop por cada célula do intervalo de destino...
    For Each DestCell In DestRng
        'Somente copia se a célula for visível (RowHeight não é 0)
        If DestCell.EntireRow.RowHeight > 0 Then
            'Verifica se o valor na célula de destino não é vazio...
            If SrcCell.Value <> "" Then
                DestCell.Value = SrcCell.Value
            End If
            
            'Determina se existem várias colunas de valores que estão sendo copiados
            If DestRngColumnCount > 1 Then
                If DestCell.Column = DestRngFirstColumn + DestRngColumnCount - 1 Then
                    'Mover para a próxima linha, resetar a coluna
                    Set DestRng = DestCell.Offset(1, (DestRngColumnCount - 1) * -1).Resize(DestRngRowCount, DestRngColumnCount)
                Else
                    'Mover para a próxima coluna
                    Set DestRng = DestCell.Offset(0, 1).Resize(DestRngRowCount, DestRngColumnCount)
                End If
                Exit For
            Else
                ''Mover para a próxima linha
                Set DestRng = DestCell.Offset(1, 0).Resize(DestRngRowCount, DestRngColumnCount)
                Exit For
            End If
        End If
    Next
Next
Application.ScreenUpdating = True

End Sub

5. Agora volte ao Visual Basic e cole o código que você copiou. Ficou mais ou menos assim:

6. Agora feche o editor (só clicar no “X” em cima da tela…)

7. Agora, que você já está novamente na tela do Excel, pressione Alt+F8. Vai aparecer esta janela para você executar a macro. Selecione ela com um clique e clique em Executar…

8. A macro “gerou” esta pequena caixa de seleção, onde você irá agora selecionar o intervalo de origem, ou seja, o que deseja copiar. Não precisa clicar no campo da caixa, é só clicar direto no intervalo com o mouse e arrastar, como faria normalmente no Excel:

9. Agora apareceu mais uma caixa de seleção, que pede para você selecionar o intervalor de destino, ou seja, intervalo que você irá colar os valores…

10. Veja que maravilha o resultado final:

11. A macro verifica também se os intervalos de origem e destino estão com a mesma quantidade de colunas e/ou linhas. Se estiverem diferentes, ou seja, se você selecionou mais colunas/linhas a mais do que deveria. Se isso ocorrer aparecerá esta mensagem:

12. Agora volte ao editor do Visual Basic, digitando Alt+F11. Selecione na janela da esquerda o módulo que você criou e clique em Remover:

Clique e “Não”…

Feche novamente o Editor e salve a planilha se desejar.

A propósito, nada impede de você salvar a planilha sem excluir a macro, talvez você queira guardar para referência futura.  Neste caso, se tentar salvar vai aparecer esta mensagem:

Mas basta clicar em “ Não”, escolher o local que deseja salvar e trocar o tipo de arquivo para este…

E agora é só salvar.

Fácil não é? Dá um pouco mais de trabalho, mas esta é a solução ideal para quando o primeiro caso não atende ou se você já tem algum tipo de automatização na planilha e deseja incrementar ou complementar com isso.

CLIQUE AQUI PARA BAIXAR A PLANILHA DE EXEMPLO COM A MACRO.

Espero que tenha ajudado você a economizar seu tempo. Grande abraço!