vba - 数据透视表创建中的过程调用或参数无效

Question

我很难找到这个错误的解决方案，我检查了很多帖子但无济于事，仍然是这条线有问题

Set ptable = pCache.CreatePivotTable( _
                            TableDestination:=DestSheet.Cells(1, 1), _
                            TableName:="PivotTable1", _
                            DefaultVersion:=6)

所以这是代码

Sub TestPivot()
            Dim mySourceData As String
            'Declare Variables 
            Dim SourceSheet As Worksheet
            Dim DestSheet As Worksheet
            'Declare Variales  row and column 
            Dim FirstRow As Long
            Dim LastRow As Long
            Dim FirstCol As Long
            Dim LastCol As Long
            Dim ptable As PivotTable
            Dim pCache As PivotCache
            
            'Set/Define Source and Destination Variables
            With ThisWorkbook
                Set SourceSheet = Worksheets("W")
                Set DestSheet = Worksheets("Pivot_W")
            End With
            'identify first row and first column of source data cell range
            FirstRow = 2
            FirstCol = 2
            'Find last row and last column of source data cell range
            'And obtain address of source data cell range
            With Sheets("W").Range("A:N")
                LastRow = Cells(Rows.Count, "A").End(xlUp).Row
                LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
                mySourceData = .Range(.Cells(FirstRow, FirstCol), .Cells(LastRow, LastCol)).Address(ReferenceStyle:=xlR1C1)
            End With

            'Insert Pivot Table
                Set pCache = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=SourceSheet.Name & "!" & mySourceData, Version:=6)
                Set ptable = pCache.CreatePivotTable(TableDestination:=DestSheet.Cells(1, 1), TableName:="PivotTable1", DefaultVersion:=6)
        
            If Not IsEmpty(ptable) Then
            MsgBox "non vide"
            End If
            With DestSheet.PivotTables("PivotTable1").PivotFields("Magasin")
            .Orientation = xlRowField
            .Position = 1
            End With
End Sub

感谢您的帮助，并感谢您阅读我的帖子。

标签： vbadynamicrangepivot-table