使用VBA定义数据透视缓存时,运行时错误'5'无效的过程调用或参数 [英] Run-time error '5' Invalid procedure call or argument when defining Pivot Cache using VBA
本文介绍了使用VBA定义数据透视缓存时,运行时错误'5'无效的过程调用或参数的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
在我的代码上定义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")
推荐答案
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屋!
查看全文