Excel Drilldown tabela dinâmica sempre na mesma planilha

Planilha Excel de rastreamento de pacotes do correio
7 de junho de 2017
Livro VBA e Macros Excel 2016 - Bill Jellen capa
Livro VBA e Macros Excel 2016 – Bill Jellen e Tracy Syrstad
24 de junho de 2017

Excel Drilldown tabela dinâmica sempre na mesma planilha

O detalhamento de dados da tabela dinâmica com o duplo clique é um excelente recurso do Excel, neste artigo é demonstrado como fazer drilldown dos dados da tabela dinâmica sempre na mesma planilha, tornando a planilha muito mais profissional.

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

Mostrar detalhes da tabela dinâmica sempre na mesma planilha 2

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

Coloque estes dados em um módulo, conforme demonstrado no vídeo. Este código é adaptável á qualquer tabela dinâmica.

Public Sub lsExpandirDados()
    On Error GoTo Sair
    
    Dim lPlanCriada         As String
    Dim lPlanilhaOriginal   As String
    
    Application.ScreenUpdating = False
    
    'Captura o nome da planilha aonde está a tabela dinâmica
    lPlanilhaOriginal = ActiveSheet.Name & "!" & ActiveCell.Address
    
    'Desabilitar mostrar alertas
    Application.DisplayAlerts = False
    
    'Limpa a planilha dados
    Sheets("Dados").Range("A:XFD").Clear
    
    'Abre os detalhes dos registros da tabela dinâmica
    Selection.ShowDetail = True
    
    'Guarda o nome da tabela criada
    lPlanCriada = ActiveSheet.Name
    
    'Copiar os dados abertos
    Selection.Copy
    
    'Colar as informações na tabela dados
    Sheets("Dados").Select
    Range("A1").Activate
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    'Excluir a planilha criada
    Worksheets(lPlanCriada).Delete
    
    'Organizar as colunas
    Selection.EntireColumn.AutoFit
    Sheets("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"
    
    'Criar o hyperlink
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
        lPlanilhaOriginal, TextToDisplay:="Voltar"
      

Sair:
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True

End Sub

No vídeo no topo deste artigo é demonstrado passo-a-passo como incluir este código fonte e adaptar a sua tabela dinâmica.

Para executar este código inclua no código fonte da planilha em que há a tabela dinâmica o seguinte código:

'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

Você pode fazer o download da planilha digitando o seu e-mail para assinar a nossa newsletter gratuita abaixo.

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

7 Comentários

  1. alan disse:

    Prezado não consegui baixar a planilha do post Excel Drilldown tabela dinâmica sempre na mesma planilha pode por gentileza manda- la para meu e mail:alanbago26@gmail.com. desde de já te agradeço pela atenção.

  2. PAULO disse:

    Obrigado pelo artigo

  3. JOSE COSTA disse:

    Excelente Artigo

  4. Augusto Lucio Mendes disse:

    Grande Marcos !
    EXCELente 🙂
    Seria possível fazer isso com uma série de dados do gráfico?
    Ex: clicar duas vezes sobre uma linha ou coluna e numa aba a parte colar os dados do Showdetail e a partir desses dados alimentar um novo gráfico detalhado?

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.