VBA将数据透视表的源数据更新到行尾 [英] VBA Updating Source Data for Pivot Table to end of row

问题描述

我试图弄清楚如何在使用VBA更改数据时将数据透视表源数据更新到行尾.我当前的代码如下:

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:

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

The error I am getting is Run time error 1004: Cannot open PivotTable source file: E:\offline\KXM2103\Data\CurrentWeek

推荐答案

要专门在VBA中进行此操作，可以尝试一下.

To do it exclusively in VBA you can try this.

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

这篇关于VBA将数据透视表的源数据更新到行尾的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！