我试图弄清楚当使用 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
我收到的错误是运行时错误 1004: 无法打开数据透视表源文件: E:\offline\KXM2103\Data\CurrentWeek
要专门在 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
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)