+ Reply to Thread
Results 1 to 3 of 3

VBA -- Creating Pivot Table from datamodel table

  1. #1
    Forum Contributor
    Join Date
    06-05-2013
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2016
    Posts
    110

    VBA -- Creating Pivot Table from datamodel table

    Dear all,

    I have data, ticket details in sheet " Backlog " which had duplicate entries. I need to prepare a summary in sheet " Backlog_Summary " for unique ticket no. entries.

    Manually, I select the entire table and create a pivot by adding it the data to a data model. This will enable to create summary by using Distinct Count. { This is my goal }

    I have the following VBA Code which I had modified based on macro recording when I did it manually. However, the following code doesn't do anything:

    Please Login or Register  to view this content.
    I've attached the macro file which has the whole code.

    Could you please help me understand the mistake and correct the code?

    Appreciate any help on the same.

    Thanks,
    Vinod Krishna
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    09-21-2009
    Location
    Belgium - Mechelen
    MS-Off Ver
    Office 365 - version 2310
    Posts
    278

    Re: VBA -- Creating Pivot Table from datamodel table

    Hi Vinod,

    I adapted the code a little, so now it should work.
    The reference to the data was not correct (for this file anyway), so i changed that.

    I also added another code in trhe attached file to see the difference.


    Please Login or Register  to view this content.
    HTH
    Attached Files Attached Files
    Even a little help can be a big help !!
    1. A sample workbook says more then words. Add problem description and solution so we can understand the problem.
    2. Your appreciation is accepted by clicking the star "Add Reputation" at the lower left of the post.
    3. If your problem is solved, mark it as [SOLVED]: See "FAQ : " https://www.excelforum.com/faq.php
    4. Use [CODE] [/CODE] tags to illustrate your code: see here

  3. #3
    Forum Contributor
    Join Date
    06-05-2013
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2016
    Posts
    110

    Re: VBA -- Creating Pivot Table from datamodel table

    Thanks MarMo. This helped me create the Pivot Table from the existing code.

    However, I'm looking for Pivot table created from a table which has been added to datamodel. This is needed to get distinct count as option in the Value Field settings.

    PFA for expected output for ready reference in the sheet " Backlog_Summary(Expected) "

    Also, I learnt that, we need to create a connection to use the table as datamodel. I got the code from the attached excel (Movies.xlsm) which is working as expected. Run the function CreateBasicModelPivot in Module2

    However, if I run the same code in my file, I get the Runtime error, Catastrophic error. Here the code that is causing the error:
    Please Login or Register  to view this content.
    Appreciate any help in fixing the same.

    Thanks,
    Vinod Krishna
    Attached Files Attached Files
    Last edited by Vinod Krishna.C; 10-05-2018 at 08:56 AM. Reason: Progressed in fixing the issue but stuck at run time error

+ 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. Replies: 6
    Last Post: 01-24-2017, 06:56 PM
  2. Creating a Pivot Table using Data Table in different file?
    By JasonMcQueen in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 04-13-2016, 11:56 AM
  3. [SOLVED] Creating a formula in a pivot table while using another calculated item in the pivot
    By mvhs1073933 in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 08-10-2015, 12:44 PM
  4. Replies: 1
    Last Post: 07-29-2015, 05:19 PM
  5. Replies: 1
    Last Post: 07-29-2015, 05:15 PM
  6. Replies: 0
    Last Post: 08-09-2013, 12:32 PM
  7. Creating a table/array from Pivot Table
    By Roktgr in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-22-2010, 07:02 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