• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 234
  • Last Modified:

Excel & VBA to create Pivot Table

I recorded a Macro of me creating a pivot table then attempted to step through the execution (after removing the one I had created) and I get an error on this line;
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "NRDetails[#All]", Version:=xlPivotTableVersion12).CreatePivotTable _
        TableDestination:="Not Ready Summary!R3C1", TableName:="PivotTable1", _
        DefaultVersion:=xlPivotTableVersion12

Open in new window


"NRDetails[#All]" refers to a table on another worksheet.
I've tried changing the name of the worksheet in case there was a remnant in memory of the table name but that didn't change anything.
What am I missing?
0
bouscal
Asked:
bouscal
  • 2
  • 2
1 Solution
 
Rory ArchibaldCommented:
Is the data sheet active when you run the code?
0
 
bouscalAuthor Commented:
Get an 'invalid procedure call' no matter which sheet is active.
0
 
Rory ArchibaldCommented:
Does the Not Ready Summary sheet exist? If so, try changing the destination argument to:
"'Not Ready Summary'!R3C1"
rather than:
"Not Ready Summary!R3C1"

Note the single quotes around the sheet name.
1
 
bouscalAuthor Commented:
Thank you, the single quotes made it work!!
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now