Note: The other languages of the website are Google-translated. Back to English

如何列出工作簿中的所有數據透視表?

假設您有一個包含多個數據透視表的大型工作簿,現在,您要列出此工作簿中的所有數據透視表,這可能嗎? 當然,本文中的以下VBA代碼將幫您一個忙。 要了解更多詳細信息,請閱讀以下文章。

使用VBA代碼列出工作簿中的所有數據透視表

Office選項卡在Office中啟用選項卡式編輯和瀏覽,並使您的工作更加輕鬆...
Kutools for Excel 解決了你的大部分問題,並將你的生產力提高了 80%
  • 重用任何東西: 將最常用或最複雜的公式,圖表等添加到您的收藏夾中,並在將來快速重用它們。
  • 超過20種文字功能: 從文本字符串中提取數字; 提取或刪除部分文字; 將數字和貨幣轉換為英文單詞。
  • 合併工具:將多個工作簿和工作表合二為一; 合併多個單元格/行/列,而不會丟失數據; 合併重複的行和總和。
  • 分割工具:根據價值將數據分割成多個工作表; 一本工作簿可轉換為多個Excel,PDF或CSV文件; 一列到多列。
  • 跳過粘貼 隱藏/過濾的行; 計數與求和 按背景色; 向多個收件人批量發送個性化電子郵件。
  • 超級過濾器: 創建高級過濾方案並應用於任何工作表; 分類 按星期,日期,頻率等 篩選 用粗體,公式,註釋...
  • 超過300個強大的功能; 適用於 Office 2007-2021 和 365; 支持所有語言; 在您的企業或組織中輕鬆部署。

箭頭藍色右氣泡 使用VBA代碼列出工作簿中的所有數據透視表

下面的VBA代碼可以幫助您列出所有數據透視表名稱及其屬性,例如源數據范圍,工作表名稱,刷新日期等。

1. 打開您要列出所有數據透視表的工作簿。

2。 按住 ALT + F11 鍵,然後打開 Microsoft Visual Basic for Applications窗口.

3。 點擊 插入 > 模塊,然後將以下代碼粘貼到 模塊窗口.

VBA代碼:列出工作簿中的所有數據透視表

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。 然後按 F5 運行此代碼的關鍵,所有數據透視表名稱,源數據范圍,工作表名稱和其他屬性都列在新工作表中,該新工作表位於活動工作表的前面,如以下屏幕快照所示:

doc-list-pivottable-1


相關文章:

如何檢查工作簿中是否存在數據透視表?

如何將多個字段添加到數據透視表?


最佳辦公效率工具

Kutools for Excel 解決了你的大部分問題,並將你的生產力提高了 80%

  • 重用: 快速插入 複雜的公式,圖表 以及您以前使用過的任何東西; 加密單元 帶密碼 創建郵件列表 並發送電子郵件...
  • 超級公式欄 (輕鬆編輯多行文本和公式); 閱讀版式 (輕鬆讀取和編輯大量單元格); 粘貼到過濾範圍...
  • 合併單元格/行/列 不會丟失數據; 拆分單元格內容; 合併重複的行/列...防止細胞重複; 比較範圍...
  • 選擇重複或唯一 行; 選擇空白行 (所有單元格都是空的); 超級查找和模糊查找 在許多工作簿中; 隨機選擇...
  • 確切的副本 多個單元格,無需更改公式參考; 自動創建參考 到多張紙; 插入項目符號,複選框等...
  • 提取文字,添加文本,按位置刪除, 刪除空間; 創建和打印分頁小計; 在單元格內容和註釋之間轉換...
  • 超級濾鏡 (將過濾方案保存並應用於其他工作表); 高級排序 按月/週/日,頻率及更多; 特殊過濾器 用粗體,斜體...
  • 結合工作簿和工作表; 根據關鍵列合併表; 將數據分割成多個工作表; 批量轉換xls,xlsx和PDF...
  • 超過 300 項強大的功能. 支持 Office / Excel 2007-2021 和 365。支持所有語言。 在您的企業或組織中輕鬆部署。 完整功能 30 天免費試用。 60 天退款保證。
kte選項卡201905

Office選項卡為Office帶來了選項卡式界面,使您的工作更加輕鬆

  • 在Word,Excel,PowerPoint中啟用選項卡式編輯和閱讀,發布者,Access,Visio和Project。
  • 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
  • 將您的工作效率提高 50%,每天為您減少數百次鼠標點擊!
officetab底部
按評論排序
留言 (20)
還沒有評分。 成為第一位評論!
網站主持人對此評論進行了最小化
好東西。 可以包含此代碼段以使數據透視表名稱成為超鏈接。 .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 .Cells(I, 7).Characters(Start:=1, Length:=Len(pt.Name)).Font .Underline = xlUnderlineStyleSingle .Color = -4165632 End With
網站主持人對此評論進行了最小化
ж執чччч。 Не могли бы слелать код более читабельным (разбить на строки и переменные)。
網站主持人對此評論進行了最小化
優秀的。 謝謝你。
網站主持人對此評論進行了最小化
這對我來說是一個巨大的發現! 希望我三天前就已經找到了這個解決方案。 它會為我節省幾個小時! 謝謝!
網站主持人對此評論進行了最小化
我需要修復一個包含 60 多個數據透視表的文件。 最初一次單擊每個進行修復(就像有人“取消鏈接”它們但我無法找到所有錯誤的情況一樣。它在原始隱藏的幾列中找到了最後一個)。 不錯的一段代碼!
網站主持人對此評論進行了最小化
我試圖在 excel 2013 中運行它,我得到運行時錯誤“1004”:應用程序定義或對象定義錯誤。 如果註釋掉 .Cells(I, 2).Value = pt.SourceData 行它會運行良好,你能告訴我我可能需要做什麼才能讓 SourceData 部分工作嗎? 謝謝羅斯
網站主持人對此評論進行了最小化
[quote]我試圖在 excel 2013 中運行它,但我得到了運行時錯誤“1004”:應用程序定義的或對象定義的錯誤。 如果註釋掉 .Cells(I, 2).Value = pt.SourceData 行它會運行良好,你能告訴我我可能需要做什麼才能讓 SourceData 部分工作嗎? 謝謝羅斯羅斯[/quote] 我遇到了同樣的錯誤。 我認為當數據透視表的源是 Excel 數據模型並由 PowerPivot 使用時,它是爆炸性的。
網站主持人對此評論進行了最小化
謝謝你。 這是一段很棒的代碼,非常有用。 正如其他人所發布的那樣,我認為它不適用於基礎數據源基於僅連接數據查詢的數據透視表。 我有一個帶有一個數據表的工作簿。 隨後,我根據這張表的數據創建了更精細的工作簿數據查詢。 查詢只是連接(避免不必要地增加工作簿的大小)。 然後,我創建了依賴於工作簿查詢中數據的數據透視表。 當我運行代碼時,它適用於傳統來源的數據透視表,但是當它基於工作簿查詢中的數據到達數據透視表時會遇到運行時錯誤。 具體來說; 它給出了一個運行時錯誤“1004”:應用程序定義的或對象定義的錯誤。 出現錯誤的代碼行是: .cells(I, 2).value = pt.SourceData 感謝您提供代碼,希望以上內容可以幫助您完善它。
網站主持人對此評論進行了最小化
很強大。 非常感謝。
網站主持人對此評論進行了最小化
對於僅連接信息,數據連接信息是 PivotCache 的屬性
我有一個工作簿,其中包含 40 多個數據透視表,其中包含 Excel 表和 SQL 服務器數據連接。 我使用以下代碼來跟踪它們


子GetPivotTableInfo()

Dim wb As Workbook
將 pvt 調暗為數據透視表
將 wsheet 調暗為工作表
將 pc 調暗為 PivotCache

設置 wb = ActiveWorkbook
對於 wb.Worksheets 中的每個 wsheet
對於 wsheet.PivotTables 中的每個 pvt
Debug.Print wsheet.Name & ": " & pvt.Name
設置 pc = wb.PivotCaches(pvt.CacheIndex)
如果 pc.SourceType = xlDatabase 那麼
調試.打印 pc.SourceData
其他
如果 pc.QueryType = xlOLEDBQuery 那麼
Debug.Print pc.Connection
調試.打印 pc.SourceConnectionFile
Debug.Print pc.WorkbookConnection.Name
調試.Print pc.CommandText
如果結束
如果結束


下一個列兵
下一個工作表
網站主持人對此評論進行了最小化
使我免於嚴重的頭痛!
找不到導致“刷新所有錯誤”的數據透視表
繁榮列出了地點,非常感謝
網站主持人對此評論進行了最小化
驚人的!!! 謝謝!!!!
網站主持人對此評論進行了最小化
這太棒了! 我一直在尋找這樣的代碼,但增加了顯示每個表的所有活動字段。 我需要從不必要的字段(太重)中清理源表,它們提供了一本包含 300 多個樞軸的書。 我不想一次去一個數據透視表來找出我可以消除哪些字段......如果你能告訴我如何,那將是不可思議的......謝謝!
網站主持人對此評論進行了最小化
感謝您的帖子,我通過定義 I 和 Long 並刪除對 K 的引用來清理代碼,因為它沒有被使用。 最後,我在 .Activate 下方添加了一行以顯示 .Columns.AutoFit
網站主持人對此評論進行了最小化
在緊要關頭非常方便! 謝謝你。
網站主持人對此評論進行了最小化
棒極了。
網站主持人對此評論進行了最小化
巨大的幫助,為我節省了大量時間來追踪工作簿中多個同名數據透視表中的一個!
網站主持人對此評論進行了最小化
運行時錯誤 1004
網站主持人對此評論進行了最小化
合身完美!! 非常感謝
網站主持人對此評論進行了最小化
不幸的是,我遇到了多個錯誤,這對於我的多選項卡、多軸工作簿來說絕對很棒
這裡還沒有評論
留下你的意見
以訪客身份發帖
×
評價此帖子:
0   字符
推薦地點

關注我們

版權所有©2009 - 萬維網。extendoffice.com。 | 版權所有。 供電 ExtendOffice。 |
Microsoft和Office徽標是Microsoft Corporation在美國和/或其他國家的商標或註冊商標。
受Sectigo SSL保護