Tipp: Andere Sprachen sind Google-Übersetzungen. Sie können die English Version dieses Links.
Einloggen
x
or
x
x
Registrieren
x

or

Wie listet man alle Pivot-Tabellen aus einer Arbeitsmappe auf?

Angenommen, Sie haben eine große Arbeitsmappe, die mehrere Pivot-Tabellen enthält, möchten Sie jetzt alle Pivot-Tabellen in dieser Arbeitsmappe auflisten, ist das möglich? Natürlich wird der folgende VBA-Code in diesem Artikel Ihnen einen Gefallen tun. Um mehr Details zu erfahren, lesen Sie bitte den folgenden Artikel.

Listen Sie alle Pivot-Tabellen aus einer Arbeitsmappe mit VBA-Code auf

Registerkarte "Office" Aktivieren Sie das Bearbeiten und Durchsuchen von Registerkarten in Office und vereinfachen Sie Ihre Arbeit erheblich ...
Kutools for Excel löst die meisten Ihrer Probleme und steigert Ihre Produktivität um 80%
  • Alles wiederverwenden: Fügen Sie die am häufigsten verwendeten oder komplexesten Formeln, Diagramme und andere Elemente zu Ihren Favoriten hinzu und verwenden Sie sie in Zukunft schnell wieder.
  • Mehr als 20-Textfunktionen: Extrahieren Sie die Nummer aus der Textzeichenfolge. Teile von Texten extrahieren oder entfernen; Zahlen und Währungen in englische Wörter umrechnen ...
  • Tools zusammenführen: Mehrere Arbeitsmappen und Blätter in einer; Mehrere Zellen / Zeilen / Spalten zusammenführen, ohne Daten zu verlieren; Doppelte Zeilen und Summe zusammenführen ...
  • Geteilte Werkzeuge: Aufteilen von Daten in mehrere Blätter basierend auf dem Wert; Eine Arbeitsmappe in mehrere Excel-, PDF- oder CSV-Dateien; Eine Spalte zu mehreren Spalten ...
  • Einfügen überspringen Versteckte / Gefilterte Zeilen; Zählung und Summe nach Hintergrundfarbe; Mailingliste erstellen und Senden Sie E-Mails nach dem Wert von Cell...
  • Super Filter: Erstellen Sie erweiterte Filterschemata und wenden Sie diese auf alle Blätter an. Art nach Woche, Tag, Häufigkeit und mehr; Filtern durch Fettdruck, Formeln, Kommentar ...
  • Mehr als 300 leistungsstarke Funktionen; Funktioniert mit Office 2007-2019 und 365; Unterstützt alle Sprachen; Einfache Bereitstellung in Ihrem Unternehmen oder Ihrer Organisation.

Pfeil blaue rechte Blase Listen Sie alle Pivot-Tabellen aus einer Arbeitsmappe mit VBA-Code auf


Der folgende VBA-Code kann Ihnen helfen, alle Pivot-Tabellennamen zusammen mit ihren Attributen aufzulisten, z. B. Quelldatenbereich, Arbeitsblattname, aktualisierbares Datum und so weiter.

1. Öffnen Sie Ihre Arbeitsmappe, in der Sie alle Pivot-Tabellen auflisten möchten.

2. Halten Sie die Taste gedrückt ALT + F11 Schlüssel, und es öffnet die Microsoft Visual Basic für Applikationen-Fenster.

3. Klicken Sie Einsatz > Modul, und fügen Sie den folgenden Code in die Modulfenster.

VBA-Code: Listet alle Pivot-Tabellen aus einer Arbeitsmappe auf

Sub ListPivotsInfor()
'Update 20141112
    Dim St As Worksheet
    Dim NewSt As Worksheet
    Dim pt As PivotTable
    Dim I, K As Long
    Application.ScreenUpdating = False
    Set NewSt = Worksheets.Add
    I = 1: K = 2
    With NewSt
        .Cells(I, 1) = "Name"
        .Cells(I, 2) = "Source"
        .Cells(I, 3) = "Refreshed by"
        .Cells(I, 4) = "Refreshed"
        .Cells(I, 5) = "Sheet"
        .Cells(I, 6) = "Location"
        For Each St In ActiveWorkbook.Worksheets
            For Each pt In St.PivotTables
                I = I + 1
                .Cells(I, 1).Value = pt.Name
                .Cells(I, 2).Value = pt.SourceData
                .Cells(I, 3).Value = pt.RefreshName
                .Cells(I, 4).Value = pt.RefreshDate
                .Cells(I, 5).Value = St.Name
                .Cells(I, 6).Value = pt.TableRange1.Address
            Next
        Next
        .Activate
    End With
    Application.ScreenUpdating = True
End Sub

4. Dann drücken F5 Zum Ausführen dieses Codes werden alle Pivot-Tabellennamen, der Quelldatenbereich, der Arbeitsblattname und andere Attribute in einem neuen Arbeitsblatt aufgelistet, das wie im folgenden Screenshot im Vordergrund des aktiven Arbeitsblatts platziert wird:

doc-list-all-pivotable-1


In Verbindung stehende Artikel:

Wie überprüft man, ob eine Pivot-Tabelle in einer Arbeitsmappe vorhanden ist?

Wie fügt man der Pivot-Tabelle mehrere Felder hinzu?


Kutools for Excel löst die meisten Ihrer Probleme und steigert Ihre Produktivität um 80%

  • Wiederverwendung: Schnell einlegen komplexe Formeln, Diagramme und alles, was du vorher benutzt hast; Zellen verschlüsseln mit Passwort; Mailingliste erstellen und E-Mails senden ...
  • Super Formula Bar (Bearbeiten Sie mühelos mehrere Textzeilen und Formeln); Layout lesen (Leichtes Lesen und Bearbeiten einer großen Anzahl von Zellen); In gefilterten Bereich einfügen...
  • Zellen / Zeilen / Spalten zusammenführen ohne Daten zu verlieren; Inhalt der Zellen teilen; Kombinieren Sie doppelte Zeilen / Spalten... Doppelte Zellen verhindern; Bereiche vergleichen...
  • Wählen Sie "Duplizieren" oder "Eindeutig" Reihen; Wählen Sie Leere Zeilen (alle Zellen sind leer); Super Find und Fuzzy Find in vielen Arbeitsmappen; Zufallsauswahl ...
  • Exakte Kopie Mehrere Zellen ohne Änderung der Formelreferenz; Referenzen automatisch erstellen auf mehrere Blätter; Bullets einfügen, Kontrollkästchen und mehr ...
  • Extract Text, Text hinzufügen, Nach Position entfernen, Leerzeichen entfernen; Erstellen und Drucken von Paging-Zwischensummen; Zwischen Zelleninhalt und Kommentaren konvertieren...
  • Superfilter (Speichere und wende Filterschemata auf andere Blätter an); Erweiterte Sortierung nach Monat / Woche / Tag, Häufigkeit und mehr; Spezialfilter fett, kursiv ...
  • Arbeitsmappen und Arbeitsblätter kombinieren; Zusammenführen von Tabellen basierend auf Schlüsselspalten; Daten in mehrere Blätter aufteilen; Stapelkonvertierung von xls, xlsx und PDF...
  • Mehr als 300 leistungsstarke Funktionen. Unterstützt Office / Excel 2007-2019 und 365. Unterstützt alle Sprachen. Einfache Bereitstellung in Ihrem Unternehmen oder Ihrer Organisation. Kostenlose 30-Testversion für alle Funktionen.
Registerkarte Tab 201905

Registerkarte "Office" Bringt die Benutzeroberfläche mit Registerkarten in Office und vereinfacht Ihre Arbeit erheblich

  • Aktivieren Sie das Bearbeiten und Lesen von Registerkarten in Word, Excel und PowerPoint, Publisher, Access, Visio und Project.
  • Öffnen und erstellen Sie mehrere Dokumente in neuen Registerkarten desselben Fensters und nicht in neuen Fenstern.
  • Steigert Ihre Produktivität um 50% und reduziert täglich Hunderte von Mausklicks für Sie!
officetab unten
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Fuzz-Head · 1 months ago
    Thanks for the post, I cleaned up the code a little by defining I and Long and deleting the ref to K since it wasn't used. Lastly I added a line just below your .Activate to show .Columns.AutoFit
  • To post as a guest, your comment is unpublished.
    Abraham Garzon · 5 months ago
    This is awesome! I have been looking for a code like this one, but with the addition of showing all active fields per table. I need to clean up the source tables from unnecessary fields (too heavy) and they feed a book of over 300 pivots. I'd prefer not to go one pivot table at a time to figure out which fields I can eliminate... If you could show me how, it would be incredible... Thanks!
  • To post as a guest, your comment is unpublished.
    Vinicius · 5 months ago
    Amazing!!! thksss!!!!
  • To post as a guest, your comment is unpublished.
    Mark · 1 years ago
    Saved me from a major headache!
    Couldn't find what pivot table was causing a "Refresh All Error"
    Boom Listed with locations, Thank you so much
  • To post as a guest, your comment is unpublished.
    chris · 1 years ago
    For Connection Only info the Data connection info isa property of the PivotCache
    I have a workbook that has over 40 pivot tables with a mix of Excel Tables and SQL server Data Connections. I use the following code to keep track of them


    Sub GetPivotTableInfo()

    Dim wb As Workbook
    Dim pvt As PivotTable
    Dim wsheet As Worksheet
    Dim pc As PivotCache

    Set wb = ActiveWorkbook
    For Each wsheet In wb.Worksheets
    For Each pvt In wsheet.PivotTables
    Debug.Print wsheet.Name & ": " & pvt.Name
    Set pc = wb.PivotCaches(pvt.CacheIndex)
    If pc.SourceType = xlDatabase Then
    Debug.Print pc.SourceData
    Else
    If pc.QueryType = xlOLEDBQuery Then
    Debug.Print pc.Connection
    Debug.Print pc.SourceConnectionFile
    Debug.Print pc.WorkbookConnection.Name
    Debug.Print pc.CommandText
    End If
    End If


    Next pvt
    Next wsheet
  • To post as a guest, your comment is unpublished.
    Turnabout · 1 years ago
    Very powerful. Thanks so much.
  • To post as a guest, your comment is unpublished.
    HDF · 2 years ago
    Thanks for this. It's a great piece of code, very useful.

    As others have posted, I think it doesn't work for pivot tables whose underlying data source is based on a Connection Only data query.

    I have a workbook with one data table. I subsequently create more refined workbook data queries based on this one table's data. The queries are connection only (avoiding an unnecessary increase in the size of the workbook). I then created pivot tables that rely on the data in the workbook queries.

    When I run the code, it works fine for traditionally sourced pivot tables, but it hits a run-time error when it gets to the pivot tables based on data in Workbook Queries. Specifically; it gives a Run-time error "1004": Application-defined or object defined error. The line of code where the error occurs is:

    .cells(I, 2).value = pt.SourceData

    Thanks for providing the code and I hope the above helps you refine it.
  • To post as a guest, your comment is unpublished.
    Ross · 2 years ago
    I have tried to run this in excel 2013 and I get the Run-time error '1004': Application-defined or object-defined error.

    If comment out the .Cells(I, 2).Value = pt.SourceData line it will run fine, can you advise me what I might need to do to get the SourceData part to work?

    Thanks Ross
    • To post as a guest, your comment is unpublished.
      EdH · 2 years ago
      [quote name="Ross"]I have tried to run this in excel 2013 and I get the Run-time error '1004': Application-defined or object-defined error.

      If comment out the .Cells(I, 2).Value = pt.SourceData line it will run fine, can you advise me what I might need to do to get the SourceData part to work?

      Thanks Ross[/quote]

      I got the same error. I think it is bombing when the source for a pivot table is the Excel Data Model and used by PowerPivot.
  • To post as a guest, your comment is unpublished.
    Dave · 3 years ago
    I needed to fix a file that had over 60 pivot tables in it. Initially clicked each one at a time to fix ( as is the case for times when someone 'unlinks' them but I could not fina all the wrong ones. It found the last one on a few columns that were hidden on the original) . Nice piece of code !
  • To post as a guest, your comment is unpublished.
    Pat Z · 3 years ago
    This was a massive find for me! Wish I had looked for this solution three days ago. It would have saved me hours!! Thanks!
  • To post as a guest, your comment is unpublished.
    Doaa · 3 years ago
    excellent. thank you.
  • To post as a guest, your comment is unpublished.
    Drip LeBuk · 4 years ago
    Good stuff. Could include this snippet to make the pivot table name a hyperlink.

    .Cells(I, 1).Parent.Hyperlinks.Add Anchor:=.Cells(I, 1) _
    , Address:="" _
    , SubAddress:="'" + St.Name + "'!" + Split(pt.TableRange1.Address, ":")(0) _
    , TextToDisplay:=pt.Name
    With .Cells(I, 7).Font
    .ColorIndex = xlAutomatic
    .Underline = xlUnderlineStyleNone
    End With
    With .Cells(I, 7).Characters(Start:=1, Length:=Len(pt.Name)).Font
    .Underline = xlUnderlineStyleSingle
    .Color = -4165632
    End With