使用VBA定义数据透视缓存时，运行时错误'5'无效的过程调用或参数 [英] Run-time error '5' Invalid procedure call or argument when defining Pivot Cache using VBA

问题描述

在我的代码上定义PivotCache时，我发现此问题运行时错误'5'无效的过程调用或参数".我正在使用的Excel是2016 MSO(16.0.4266.1001)32位.VBA 7.

I am finding this issue "Run-time error '5' Invalid procedure call or argument" when defining the PivotCache on my code. The Excel I am using is 2016 MSO (16.0.4266.1001) 32 bit. VBA 7.

'Declare Variables
Dim PSheet As Worksheet
Dim DSheet As Worksheet
Dim PCache As PivotCache
Dim PTable As PivotTable
Dim PRange As Range
Dim LastRow As Long
Dim LastCol As Long

'Sort Age of Case
LastRow = Cells(Rows.Count, 2).End(xlUp).Row
Range("M" & LastRow).Sort Key1:=Range("M3:M" & LastRow), _
   Order1:=xlAscending, Header:=xlYes

'Insert a New Blank Worksheet
On Error Resume Next

Application.DisplayAlerts = False
Worksheets.Add(After:=Worksheets(1)).Name = "US MASTER"
Application.DisplayAlerts = True

Set PSheet = Worksheets("US MASTER")
Set DSheet = Worksheets("US Master Macro")

'Define Data Range
LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row

'
'Pivot Table 1
'

'Define Pivot Cache
Set PCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=PRange)

'Insert Blank Pivot Table
Set PTable = PCache.CreatePivotTable _
(TableDestination:=PSheet.Range("B3"), TableName:="Total Backlog")

推荐答案

创建 PivotCache 时，

When creating a PivotCache, MSDN recommends using a String reference to specify the workbook, worksheet and cell range for SourceData, instead of passing a Range object.

代替使用 PRange (您在这里忘记了 Set )，可以使用R1C1表示法，例如

Instead of using PRange (which you've forgotten to Set here), you can use R1C1 notation, like this answer suggests.

LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column

... SourceData:="'US Master Macro'!R1C1:R" & LastRow & "C" & LastCol

这篇关于使用VBA定义数据透视缓存时，运行时错误'5'无效的过程调用或参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！