Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel & VBA to create Pivot Table

Posted on 2011-03-17
4
Medium Priority
?
229 Views
Last Modified: 2012-05-11
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
Comment
Question by:bouscal
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35158391
Is the data sheet active when you run the code?
0
 
LVL 7

Author Comment

by:bouscal
ID: 35158530
Get an 'invalid procedure call' no matter which sheet is active.
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 2000 total points
ID: 35158571
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
 
LVL 7

Author Closing Comment

by:bouscal
ID: 35159074
Thank you, the single quotes made it work!!
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question