如何在生成数据透视表时使用记录宏(VBA)中的动态源数据

Sub CreateDynamicPivotTable()
    Dim wsSource As Worksheet
    Dim wsDest As Worksheet
    Dim pc As PivotCache
    Dim pt As PivotTable
    Dim rngSource As Range
    
    ' 设置源数据工作表和范围
    Set wsSource = ThisWorkbook.Sheets("Sheet1")
    Set rngSource = wsSource.Range("A1:D100")
    
    ' 设置目标工作表
    Set wsDest = ThisWorkbook.Sheets("Sheet2")
    wsDest.Select
    
    ' 创建数据透视表缓存
    Set pc = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rngSource)
    
    ' 创建数据透视表
    Set pt = pc.CreatePivotTable(TableDestination:=wsDest.Range("A3"), TableName:="PivotTable1")
    
    ' 添加字段到数据透视表
    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").Position = 1
    End With
End Sub