vba - 如何为数据透视表动态选择数据范围

Question

我已经详尽地搜索了这个主题，但是我正在努力寻找一个适用于我的宏的解决方案。我需要数据透视表的源数据以包含工作表上的所有行(包含数据)。行数每天都会变化。

这是我到目前为止所得到的:

    Sheets("Sheet1").Select
    Sheets("Sheet1").Name = "RAW_DATA"
    Range("A1").Select
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "RAW_DATA!R1C1:R159C24", Version:=xlPivotTableVersion14).CreatePivotTable _
        TableDestination:="Sheet4!R3C1", TableName:="PivotTable1", DefaultVersion _
        :=xlPivotTableVersion14
    Sheets("Sheet4").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Asset")
        .Orientation = xlRowField
        .Position = 1
    End With

代表我的数据透视表源数据的值是 RAW_DATA!R1C1:R159C24 .问题是我需要这个范围根据填充的源数据范围的大小动态增加或减少。

Answer 1

首先，您可以通过将列设置为数据范围(例如 RAW_DATA!$A:$X)轻松解决您的问题。
附加数据后，数据透视表上的简单更新将包括新数据或排除不再存在的数据。

也就是说，这是一个 VBA 解决方案:
此示例会将 Sheet1 上 PivotTable1 的源数据更改为“RAW_DATA!$A$1:$X$ 不管最后一行是什么”

Sub ChangePivotData()
Dim lastrow as double

lastrow = Worksheets("RAW_DATA").Range("A" & Rows.Count).End(xlUp).Row

With Worksheets("Sheet1").PivotTables("PivotTable1")
    .ChangePivotCache ActiveWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, _
        SourceData:="RAW_DATA!A2:X" & CStr(lastrow), _    
        Version:=xlPivotTableVersion14)
End With
End Sub

这就是它的核心。但是，您可能希望检查空白列标题以防止错误，在设置新缓存后自动刷新表等。

更广泛的例子可以在这里找到:
http://www.thespreadsheetguru.com/the-code-vault/2014/7/9/change-a-pivot-tables-data-source-range

享受 :)