代码之家 › 专栏 › 技术社区 › osygl

Excel VBA透视表缓存方法错误

vba pivot-table caching excel

osygl · 技术社区 · 8 年前

我试图执行一个宏,该宏基本上从动态范围创建一个透视表,然后利用这些数据。

数据透视表保存在一个工作表中,每次执行宏时,它都会删除该工作表中的数据透视表,然后更新数据范围,并使用新范围再次生成数据透视表。

但是,尝试生成缓存的代码部分会导致错误:

“无效的过程调用或参数”。

代码如下。

Sub Weeks_Coverage_Calc()
'Creates a pivot table to calculate stock weeks coverage for every item

Dim sht As Worksheet
Dim Activesht As Worksheet
Dim pvtCache As PivotCache
Dim pvt As PivotTable
Dim StartPvt As String
Dim SrcData As String
Dim lastRow As Integer
Dim Rng As Range
Dim Row As Range

'Clear the worksheet for pivot table
    Set sht = ActiveWorkbook.Sheets("Weeks Coverage")
    For Each pvt In sht.PivotTables
    pvt.PivotSelect "", xlDataAndLabel, True
    Selection.Delete Shift:=xlToLeft
    Next pvt
'Set Transfer sheet as active sheet
 Set Activesht = ActiveWorkbook.Sheets("Transfer")

'Determine the dynamic range
 lastRow = Activesht.Range("B1000000").End(xlUp).Row
 Set Rng = Activesht.Range("B4:AF" & lastRow)


'Determine the data range you want to pivot
  SrcData = ActiveSheet.Name & "!" & Rng.Address(ReferenceStyle:=xlR1C1)

'Initialize the cell to start the table
  StartPvt = sht.Name & "!" & sht.Range("A3").Address(ReferenceStyle:=xlR1C1)

'Create Pivot Cache from Source Data
  Set pvtCache = ActiveWorkbook.PivotCaches.Create( _
    SourceType:=xlDatabase, _
    SourceData:=SrcData)


    'Refresh pivot cache
        For i = 1 To Worksheets("Weeks Coverage").PivotTables.Count
        Worksheets("Weeks Coverage").PivotTables(i).PivotCache.Refresh
        Next i


'Create Pivot table from Pivot Cache
    Set pvt = pvtCache.CreatePivotTable( _
    TableDestination:=StartPvt, _
    TableName:="PivotTable1")

'Add fields to Pivot Table

    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Item ID")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Stok"), "Sum of Stok", xlSum

    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Sales"), "Sum of Sales", xlSum

1 回复 | 直到 7 年前

Shai Rado 8 年前

尝试设置 PivotTable PivotCache

'Determine the data range you want to pivot
SrcData = Rng.Address(False, False, xlA1, xlExternal)

'Create Pivot Cache from Source Data
Set pvtCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
            SourceData:=SrcData)

'Create Pivot table from Pivot Cache
Set pvt = sht.PivotTables.Add(PivotCache:=pvtCache, TableDestination:=sht.Range("A3"), _
            TableName:="PivotTable1")