+ Reply to Thread
Results 1 to 2 of 2

VBA Created Pivot Table Doesn't Show Data

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    06-03-2008
    Posts
    387

    VBA Created Pivot Table Doesn't Show Data

    I've written some VBA to create a pivot table. It works fine. The only problem is that when it's first created, it only shows the headings. As soon as I click on one of them, then all of the values show-up.

    I've tried refreshing the Pivot Table in the code, but that doesn't work.

    Sub createPivotTable()
        Dim WSD As Worksheet
        Dim PTCache As PivotCache
        Dim PT As PivotTable
        Dim PRange As Range
        Dim FinalRow As Long
        Dim FinalCol As Long
        Set WSD = Worksheets("SKU Pricing")
        
        ' Define input area and set up a Pivot Cache
        FinalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).row
        FinalCol = WSD.Cells(1, Application.Columns.Count).End(xlToLeft).Column
        Set PRange = WSD.Cells(1, 1).Resize(FinalRow, FinalCol)
        Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
            SourceData:=PRange)
        Sheets.Add
        ActiveSheet.Name = "SKU Pivot"
        Set PT = PTCache.createPivotTable(TableDestination:=Worksheets("SKU Pivot").Cells(2, 2), _
        TableName:="PivotTable1")
        PT.ManualUpdate = True
        ' Set up the row & column fields
        PT.AddFields RowFields:=Array("Option Name & Description", "SKU", "QTY"), _
            ColumnFields:="Style Name"
        ' Set up the data fields
        With PT.PivotFields("SKU Net Price")
            .Orientation = xlDataField
            .Function = xlSum
            .Position = 1
        End With
        For Each PT In Worksheets("SKU Pivot").PivotTables
            PT.RefreshTable
        Next PT
    End Sub
    Last edited by yawnzzzz; 10-08-2008 at 10:21 AM.

  2. #2
    Forum Contributor
    Join Date
    06-03-2008
    Posts
    387
    Whoops... forgot to change the Manual Update back to False.

    PT.ManualUpdate = False

+ 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. depending data in pivot table
    By ncb in forum Excel General
    Replies: 6
    Last Post: 10-06-2008, 06:12 AM
  2. Extracting data from pivot table??
    By kev_newc123 in forum Excel General
    Replies: 2
    Last Post: 07-22-2008, 06:28 AM
  3. Re-Post - Data Repeats in Pivot Table
    By hutch@edge.net in forum Excel General
    Replies: 13
    Last Post: 06-24-2008, 03:08 PM
  4. Return pivot table range...not the data table, the PIVOT TABLE!
    By Air_Cooled_Nut in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-26-2008, 01:07 PM
  5. How to edit the Pivot Table data range?
    By smartcard in forum Excel General
    Replies: 1
    Last Post: 12-26-2006, 04:57 AM

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