vba VBA将数据透视表的源数据更新到行尾
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13258001/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA Updating Source Data for Pivot Table to end of row
提问by kmiao91
I am trying to figure out how to update my pivot table source data to the end of row when the data changes using VBA. My current code is below:
我试图弄清楚如何在使用 VBA 更改数据时将数据透视表源数据更新到行尾。我当前的代码如下：
Dim shBrandPivot As Worksheet
Dim shCurrentWeek As Worksheet
Dim shPriorWeek As Worksheet
Dim shPivot As Worksheet
Dim lr As Long
Set shBrandPivot = ActiveWorkbook.Sheets("Brand Pivot")
Set shCurrentWeek = ActiveWorkbook.Sheets("Current Week")
Set shPriorWeek = ActiveWorkbook.Sheets("Prior Week")
Set shPivot = ActiveWorkbook.Sheets("Pivot")
lr = shCurrentWeek.Range("A" & Rows.Count).End(xlUp).Row
With ActiveWorkbook.Sheets("Pivot").Activate
ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:="CurrentWeek!A3:X & lr"
End With
The error I am getting is Run time error 1004: Cannot open PivotTable source file: E:\offline\KXM2103\Data\CurrentWeek
我得到的错误是运行时错误 1004：无法打开数据透视表源文件：E:\offline\KXM2103\Data\CurrentWeek
采纳答案by scott
To do it exclusively in VBA you can try this.
要仅在 VBA 中执行此操作，您可以尝试此操作。
Dim shBrandPivot As Worksheet
Dim shCurrentWeek As Worksheet
Dim shPriorWeek As Worksheet
Dim shPivot As Worksheet
Dim lr As Long
dim rng as range
Set shBrandPivot = ActiveWorkbook.Sheets("Brand Pivot")
Set shCurrentWeek = ActiveWorkbook.Sheets("Current Week")
Set shPriorWeek = ActiveWorkbook.Sheets("Prior Week")
Set shPivot = ActiveWorkbook.Sheets("Pivot")
lr = shCurrentWeek.Range("A" & Rows.Count).End(xlUp).Row
set rng = shcurrentweek.range("A3:X" & lr)
With shPivot.PivotTables(1).PivotCache
.SourceData = rng.Address(True, True, xlR1C1, True)
.Refresh
End With
