通过VBA更改Excel中所有数据透视表的数据源范围 [英] Change Data Source Range for ALL Pivot Tables within Excel via VBA

问题描述

我的工作簿由100多个数据透视表（PT）组成。大多数PT位于各自的工作表（WS）上。有些人共享工作表。 ALL从相同的数据源范围（DSR）绘制：  " G2 $ C $ 2：$ CLM $ 7  ！ G2是
保存所有数据被拉入每个PT的工作表。第2行包含命名该数据的所有标题。  C列标识从中抽取数据的会计年度，D列标识数据从中抽取的季度。

我希望能够为整个PT中的所有PT更改此DSR工作簿（WB）到新范围。 从上面说到另一个工作表和范围，通过分配给切换开关的VBA宏，当我点击它时将要求新工作表和新范围
，然后在更改后进行数据刷新。

虽然我已经查看过这里发布的其他问题，但我太了解他们如何完全了解我的情况或如何修复它。

请帮忙。 我这样做的时间太长了。

解决方案

好的 - 让我们看看我们是否可以帮助您入门。

如果创建了所有的Pivot表以重用相同的数据源，那么它们都使用相同的PivotCache，即所有数据透视表的数据都基于。这意味着您可以进行一次SourceData更改并更新所有数据透视表。

假设所有标头完全相同，那么此代码可能会让您前往解决方案。你可以通过你的切换按钮点击事件来调用它：

 public sub ChangePivSource（）
 Dim curPiv As Excel.PivotTable 
 Dim curCache As Excel .PivotCache 
 
'<添加一些错误处理>'
设置curPiv = Excel.ThisWorkbook.Sheets（" MySheetWithAPivotTable"）。PivotTables（1）
设置curCache = curPiv .PivotCache 
 strOldSourceData = curCache.SourceData'如果你好奇的话，在这里设置一个休息点，看看这实际上是什么样子'
 strMyNewSourceDataReference ="'The Alternate Sheet'！

C

2



My workbook is comprised of 100+ Pivot Tables (PT). Most PTs are located on their own individual worksheet (WS). Some share a worksheet. ALL draw from the same Data Source Range (DSR):  "G2!$C$2:$CLM$7.   G2 is the worksheet that holds all of the data being pulled into each PT. Row 2 contains all of the headers naming that data.  Column C identifies the fiscal year that the data was drawn from and Column D identifies the Quarter from which the data draws from.

I want the ability to change this DSR for ALL PTs in the ENTIRE workbook (WB) to a new range.  From the above to say another worksheet and range, via a VBA macro that is assigned to a toggle switch that will ask for the new worksheet and the new range when I click it, and then do a data refresh once changed.

While I have looked at other questions posted on here, I am too much of a novice to understand how they completely re4late to my situation or how to fix it.

Please help.  I've been at this way too long.



解决方案

OK - Let's see if we can get you started.

If all of your Pivot tables have been created to re-use the same data source, then they all use the same PivotCache which is the data all of your pivot tables are based on. That means you can make one SourceData change and have all the Pivot tables update.

Assuming All of the headers are exactly the same, then this code might get you headed to a solution. You would call this from your toggle button click event:

public sub ChangePivSource()
    Dim curPiv As Excel.PivotTable
    Dim curCache As Excel.PivotCache
   
'<Add Some Error Handling>'
    Set curPiv = Excel.ThisWorkbook.Sheets("MySheetWithAPivotTable").PivotTables(1)
    Set curCache = curPiv.PivotCache
    strOldSourceData = curCache.SourceData      'If you are curious, Set a break here to see what this actually looks like'
    strMyNewSourceDataReference = "'The Alternate Sheet'!

C

2:

这篇关于通过VBA更改Excel中所有数据透视表的数据源范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！