Mostrar Detalhes da Tabela Dinâmica Sempre na Mesma Planilha

Planilhando
Planilhando
31 de julho de 2011
Power Pivot - Visão Geral - Excel 2010
Power Pivot – Visão Geral – Excel 2010
11 de agosto de 2011

Objetivo: Demonstrar a criação de uma tabela dinâmica que abre os detalhes da tabela dinâmica sempre na mesma tabela e também a criação de um hiperlink automaticamente para voltar a planilha da tabela dinâmica.

A tabela dinâmica é uma das melhores ferramentas do Excel, porém ela possui algumas limitações como por exemplo a que demonstro como burlar neste artigo.

Uma das principais funcionalidades de uma tabela dinâmica é realizar o Drill Drown dos dados, exportando estas informações para uma nova planilha, o problema é exatamente este, sempre é uma nova planilha, e logo você tem que ficar excluindo sempre estes dados.

No código comentado abaixo e na planilha em anexo, nós temos um exemplo de como, ao realizar o Drill Drown, abrir estes dados sempre em uma mesma planilha e criar também um hiperlink automaticamente para voltar a planilha inicial.

A planilha se torna bastante prática, dado que você não perderá mais o tempo de excluir a planilha criada, e também não precisará mais ficar procurando a planilha aonde você estava antes.

Abaixo o código comentado, ele deve ser colocado na planilha aonde está a tabela dinâmica:

Sub lsExpandirDados()
    On Error GoTo Sair
    
    Dim lPlanCriada As String
    Dim lPlanilhaOriginal As String

    'Captura o nome da planilha aonde está a tabela dinâmica
    lPlanilhaOriginal = ActiveSheet.Name & "!" & ActiveCell.Address

    'Desabilita mostrar alertas de mostrar mensagens de erro
    Application.DisplayAlerts = False

    'Limpa a planilha dados
    Sheets("Dados").Range("A:XFD").Clear

    'Abre os detalhes do registro da tabela dinâmica
    Selection.ShowDetail = True

    'Guarda o nome da planilha criada
    lPlanCriada = ActiveSheet.Name
    
    'Copia os dados abertos
    Selection.Copy
    
    'Cola as informações na tabela dados
    Sheets("Dados").Select
    Sheets("Dados").Cells(1, 1).Activate
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
        
    'Exclui a planilha criada
    Worksheets(lPlanCriada).Delete
        
    'Organiza as colunas
    Selection.EntireColumn.AutoFit
    Worksheets("Dados").Range("B2").Select
    ActiveWindow.FreezePanes = True
    
    
    Worksheets("Dados").Range("A1").Select
    Selection.End(xlToRight).Select
    
    Worksheets("Dados").Cells(1, ActiveCell.Column + 2).Select

    ActiveCell.Value = "Voltar"
    
    'Cria o hiperlink
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
        lPlanilhaOriginal, TextToDisplay:="Voltar"
        
Sair:

End Sub

'Evento que é chamado antes do duplo clique da planilha
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    'Sub que realiza a abertura dos dados
    lsExpandirDados
End Sub

Abraço

Marcos Rieper


Clique aqui e leia mais sobre Excel VBA. https://www.guiadoexcel.com.br/vba/ O Guia do Excel foi criado por Marcos Rieper e oferece artigos, dicas, tutoriais e modelos de planilhas prontas. Aqui você encontra tudo sobre Excel, seja de nível básico, intermediário,  avançado e VBA. O Guia do Excel oferece diversos materiais completamente gratuitos para download. Navegue em nosso site e confira! Conheça também a nossa Loja do Excel https://loja.guiadoexcel.com.br/
Cursos

Curso Excel Completo – Curso Excel Básico + Curso Excel Avançado – Acesso Vitalício

R$218,00 R$179,00

COMPRAR
Cursos

Curso Excel Master – Curso Excel Básico + Curso Excel Avançado + Curso VBA Excel + LP – Acesso Vitalício

R$357,00 R$249,00

COMPRAR
Cursos

Curso Excel PRO – Curso Excel Avançado + Curso VBA Excel + Lógica de programação – Acesso Vitalício

R$258,00 R$199,00

COMPRAR
Cursos

Curso Excel Web – Curso VBA Excel + Lógica de programação + Curso Web Scraping VBA- Acesso Vitalício

R$388,90 R$309,00

COMPRAR

10 Comentários

  1. Hebert disse:

    Neste código como eu resolver o seguinte problema.
    – Ao dar duplo clique em uma célula que tenha dados agrupados, exemplo de meses agrupados em anos, o correto era expandir os dados, mas devido ao duplo clique o código é acionado e a planilha onde está a tabela dinâmica é excluída.

    Obrigado.

  2. Rodrigo disse:

    Show ! Perfeito ! Obrigado por compartilhar.

  3. souexcel disse:

    Tem como fazer isso também para gráfico dinâmico?
    Eu tenho uma planilha que o meu problema é justamente o fato de a cada clique ele criar um gráfico novo.
    Com isso eu gostaria de saber como fazer para apagar os gráficos já criados ou então criar uma forma de ele sempre apagar o gráfico já existente e criar um outro com o mesmo nome, assim ele apagaria o existente (gráf1, por exemplo) e criaria um outro com o mesmo nome.

    • Marcos Rieper disse:

      Boa tarde souexcel,

      Sim, tem como fazer, o princípio é basicamente o mesmo, no caso ele copia os dados que são exibidos na planilha criada, cola na outra planilha e depois exclui a que foi criada.

      Para isso terá que programar um pouco em VBA.

      Abraço

      Marcos Rieper

  4. Boa tarde.
    Ao depurar o código acima aparece a mensagem de erro de sintaxe. Onde está o erro? O meu excel é 2007.

    ‘Captura o nome da planilha aonde está a tabela dinâmica
    lPlanilhaOriginal = ActiveSheet.Name & “!” & ActiveCell.Address

  5. Bom dia Marcos.
    Obrigada pelo retorno. Quando retirei a palvra & que aparece no seu código esse erro não ocorreu mais. Assim como a palavra no final do código. Mas, quando clico duas vezes na tabela dinamica não aparece o link voltar. É como se não tivesse rodado o código.

  6. Bom dia Marcos.
    Obrigada pelo retorno. Quando retirei a palavra & que aparece no seu código esse erro não ocorreu mais. Assim como a palavra no final do código. Mas, quando clico duas vezes na tabela dinamica não aparece o link voltar. É como se não tivesse rodado o código.

  7. Que coisa louca! Quando escrevo a palavra “amp;” e “” não aparece no comentário…

Deixe uma resposta

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

Esse site utiliza o Akismet para reduzir spam. Aprenda como seus dados de comentários são processados.

Inscreva-se no nosso canal do Youtube!


Junte-se ao nosso canal do Youtube. Começamos em abril de 2016, mas já temos mais de 06:00 h de treinamentos gratuitos e este número irá aumentar. Vídeos novos todos os sábados.