VBA定义PivotCache时出现Run-time error '5'无效过程调用或参数问题

Hey there, let's break down why you're hitting that "Invalid procedure call or argument" error when setting up your PivotCache. The main issue jumps out right away: you're using the PRange variable as the source data for your PivotCache, but you never actually assign any value to PRange before that line runs. Since it's uninitialized (it's just Nothing at that point), passing it to PivotCaches.Create throws the error.

Here's how to fix it: #

First, you need to define the full range of your source data (including headers) and assign it to PRange before creating the PivotCache. You already grab the last row from column A, but you should also get the last column to make sure you include all your data.

Update your code like this:

'Define Data Range
LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
LastCol = DSheet.Cells(3, Columns.Count).End(xlToLeft).Column 'Assuming your headers are in row 3
Set PRange = DSheet.Range(DSheet.Cells(3, 1), DSheet.Cells(LastRow, LastCol))

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

A few extra things to check: #

• Double-check that your header row is correctly referenced (row 3 in the example above). If your headers are in a different row, adjust that number in DSheet.Cells(3,1) to match.
• Confirm that DSheet is pointing to the right worksheet ("US Master Macro")—your code already does this, but it's worth making sure the name matches exactly (Excel isn't case-sensitive here, but better safe than sorry).
• If you still run into issues, you can try passing the source data as a string reference instead of a range object (though using the range is preferred):

  Set PCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=DSheet.Name & "!" & PRange.Address)
  

Once you properly initialize PRange with your actual data range, that error should go away, and your PivotCache will be created successfully.

内容的提问来源于stack exchange，提问作者Myriam Diaz Martinez