+ Reply to Thread
Results 1 to 4 of 4

Using PivotCaches.Create when source data is variant

  1. #1
    Registered User
    Join Date
    07-13-2016
    Location
    Stockholm
    MS-Off Ver
    2013
    Posts
    27

    Using PivotCaches.Create when source data is variant

    I have an array in VBA. I want to create a pivot table. How do I do that? normally I use the following code:

    Set pvtCache = ActiveWorkbook.PivotCaches.Create( _
    SourceType:=xlDatabase, _
    SourceData:=SourceData)

    But now hen my source data is a variant, I will probably need abnother sourcetype than xlDatabase?

  2. #2
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Using PivotCaches.Create when source data is variant

    Hi,

    You can't directly use an array as the source data for a pivot cache. You can write it to a range and use that, or you could create a recordset from it, but it might be easier to just use whatever the original source for the array was.
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  3. #3
    Registered User
    Join Date
    07-13-2016
    Location
    Stockholm
    MS-Off Ver
    2013
    Posts
    27

    Re: Using PivotCaches.Create when source data is variant

    "You can write it to a range and use that"
    Can you please explain that?

  4. #4
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Using PivotCaches.Create when source data is variant

    Here is an example, assuming your array variable is 2D, 0-based, and called myArray
    Please Login or Register  to view this content.
    you can then build a pivot table as normal.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Run-time error 440: Method create of object 'PivotCaches' failed
    By JohnMurray in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-18-2017, 08:33 AM
  2. Replies: 6
    Last Post: 06-18-2015, 11:02 AM
  3. Replies: 1
    Last Post: 06-09-2015, 05:56 AM
  4. How to create a print button for multiple pages with variant ranges?
    By Talk_4 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-24-2015, 03:50 AM
  5. Automating data import from multiple xls files to create 1 data source.
    By Anthem12 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-03-2012, 05:00 PM
  6. VBA to create a data source for word...
    By peakymatt in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-13-2009, 06:30 AM
  7. [SOLVED] Create Array for Data Source
    By John Michl in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 03-17-2006, 03:35 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1