VBA透视表动态范围

Sub CreatePivotTable()
    Dim wsSource As Worksheet
    Dim wsDest As Worksheet
    Dim pc As PivotCache
    Dim pt As PivotTable
    Dim rngSource As Range
    Dim rngDest As Range
    
    ' 设置源工作表和目标工作表
    Set wsSource = ThisWorkbook.Sheets("Sheet1")
    Set wsDest = ThisWorkbook.Sheets("Sheet2")
    
    ' 设置动态数据范围（假设数据在A1:D100）
    Set rngSource = wsSource.Range("A1").CurrentRegion
    
    ' 创建一个新的透视表缓存
    Set pc = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rngSource)
    
    ' 在目标工作表中创建一个新的透视表
    Set rngDest = wsDest.Range("A3")
    Set pt = pc.CreatePivotTable(TableDestination:=rngDest, TableName:="DynamicPivotTable")
    
    ' 设置透视表的字段
    With pt
        .PivotFields("Column1").Orientation = xlRowField
        .PivotFields("Column1").Position = 1
        .PivotFields("Column2").Orientation = xlColumnField
        .PivotFields("Column2").Position = 1
        .PivotFields("Column3").Orientation = xlDataField
        .PivotFields("Column3").Function = xlSum
        .PivotFields("Column3").Value = "Sum of Column3"
    End With
End Sub