+ Reply to Thread
Results 1 to 11 of 11

Create a Pivot Table with VBA

  1. #1
    Registered User
    Join Date
    09-11-2014
    Location
    San Francisco, CA
    MS-Off Ver
    2013
    Posts
    24

    Create a Pivot Table with VBA

    Hello,

    I'm trying to write a code that will create a pivot table. I'm try to do this using a pivot table cache, but I can't seem to be able to get it working the way I want to.

    The attached workbook has two spreadsheets. The first sheet is a set of data, the second is the hoped-for pivot table. I'd like to have a code that generates this pivot table (the generated pivot table should be in a separate worksheet from the data). The row labels = city, values = count of Number of Complaints, values = sum of Number of Complaints.

    Thank you for any ideas on how to make this work. I greatly appreciate it!
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,637

    Re: Create a Pivot Table with VBA

    do you have any coding done? paste to see can help you debug it

    pretty good write up here if you are stuck on certain thing
    http://www.thespreadsheetguru.com/bl...l-pivot-tables
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Registered User
    Join Date
    09-11-2014
    Location
    San Francisco, CA
    MS-Off Ver
    2013
    Posts
    24

    Re: Create a Pivot Table with VBA

    Hi,

    Your cheat sheet is very clever. Thank you for sharing! I tried tweaking a few things and I'm still getting error messages. Here is what I have so far:

    Sub CreatePivotTable()

    Dim sht As Worksheet
    Dim pvtCache As PivotCache
    Dim pvt As PivotTable
    Dim StartPvt As String
    Dim SrcData As String

    'pivot table range
    SrcData = ActiveSheet.Name & "!" & Range("A:G").Address(ReferenceStyle:=xlR1C1)

    'new worksheet
    Set sht = Sheets.Add

    'pivot table placement
    StartPvt = sht.Name & "!" & sht.Range("A3").Address(ReferenceStyle:=xlR1C1)

    'pivot cache from data
    Set pvtCache = ActiveWorkbook.PivotCaches.Create( _
    SourceType:=xlDatabase, _
    SourceData:=SrcData)

    'create table
    Set pvt = pvtCache.CreatePivotTable( _
    TableDestination:=StartPvt, _
    TableName:="Table")

    'create row label
    pvt.PivotFields("city").Orientation = xlRowField

    'total sum of number of complaints
    Dim pf As String
    Dim pf_Name As String

    pf = "Complaints"
    pf_Name = "Sum of Number of Complaints"

    Set pvt = ActiveSheet.PivotTables("PivotTable1")

    pvt.AddDataField pvt.PivotFields("Complaints Sum"), pf_Name, xlSum

    'total count of number of complaints
    pvt.AddDataField pvt.PivotFields("Complaints Counts"), pf_Name, xlCount

    End Sub

  4. #4
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,637

    Re: Create a Pivot Table with VBA

    Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

  5. #5
    Registered User
    Join Date
    09-11-2014
    Location
    San Francisco, CA
    MS-Off Ver
    2013
    Posts
    24

    Re: Create a Pivot Table with VBA

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    09-11-2014
    Location
    San Francisco, CA
    MS-Off Ver
    2013
    Posts
    24

    Re: Create a Pivot Table with VBA

    Thanks for the advice, still learning to use the forum tools. I agree, that definitely helps make it readable.

  7. #7
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,637

    Re: Create a Pivot Table with VBA

    not a problem
    it helps people who search and may use the thread later

    i made some amendments on the code
    mainly the Srcdata was setting wrong
    rest is just syntax errors

    Please Login or Register  to view this content.
    Set SrcData = ActiveSheet.Cells(1, 1).Resize(lrow, 7)

    7 represents the 7 columns
    should you have more or less...just change that number
    lRow is calculated using code so don't worry about it

    make sure to only run the code when on data sheet or it breaks down

  8. #8
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,637

    Re: Create a Pivot Table with VBA

    i didnt use PF_Name variable because you need it to be two different things
    one is sum and one is count

  9. #9
    Registered User
    Join Date
    09-11-2014
    Location
    San Francisco, CA
    MS-Off Ver
    2013
    Posts
    24

    Re: Create a Pivot Table with VBA

    Thanks for the help. I was trying it again today but it the code would stop when it hit the last two lines and it wouldn't finish the pivot table.

    Any ideas?

    Please Login or Register  to view this content.

  10. #10
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,637

    Re: Create a Pivot Table with VBA

    did you change the data at all?

    i don't seem to have the same issue as you
    see attached example
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    09-11-2014
    Location
    San Francisco, CA
    MS-Off Ver
    2013
    Posts
    24

    Re: Create a Pivot Table with VBA

    I found the error in the last line! Ok, thank you so much for your help on this. These reports are going to run a lot smoother now

+ 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. Create a macro to create a pivot table in a worksheet
    By Triscia in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-15-2015, 10:53 AM
  2. Macro to create pivot table/pivot chart
    By Karina in forum Excel General
    Replies: 2
    Last Post: 10-27-2011, 10:33 PM
  3. [SOLVED] Create Pivot Table: Cannot Open Pivot Table Source File
    By jomili in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 01-05-2010, 05:11 AM
  4. Create pivot table from exisiting pivot table
    By cmb80 in forum Excel General
    Replies: 1
    Last Post: 09-18-2009, 07:10 AM
  5. How do I create multiple pivot charts from one pivot table?
    By Rudy in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 03-17-2006, 06:10 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