Results 1 to 2 of 2

Pivot Table not being created by VBA Code

Threaded View

  1. #1
    Registered User
    Join Date
    02-21-2020
    Location
    Davis, CA
    MS-Off Ver
    Office 365 Professional 2019
    Posts
    1

    Pivot Table not being created by VBA Code

    I have been struggling to create a pivot table using the following piece of VBA code, but I cannot find the error. I assigned a watch window to PT2, and I noticed after I set PT2 equal to the PivotCache, PTCache2, PT2 still appears with a value of nothing. The With Block does not work because VBA thinks PT2 is an empty object. I am pulling data from a table, GroupDetects, on another sheet, Detects. I am using Excel 2016.

    Sub GroupDetectsPivot()
        Dim PTCache2 As PivotCache
        Dim PT2 As PivotTable 'GroupDetectsPivot PivotTable
        
    'Create the cache for the ChemicalDetectsPivot Pivot Table
            Set PTCache2 = ActiveWorkbook.PivotCaches.Create( _
            SourceType:=xlDatabase, _
            SourceData:="GroupDetects")  'GroupDetects table on the Detects sheet
    
        Sheets("DetectsPivot").Activate
        Set PT2 = ActiveSheet.PivotTables.Add( _
            PivotCache:=PTCache2, _
            TableDestination:=Range("F1"), _
            TableName:="GroupDetectsPivot") 'Name the pivot table
            
    'Set up GroupDetectsPivot to count detects by group
        With PT2
        .ColumnGrand = False 'Removes grand total row
        .RowGrand = False 'Removes grand total column
        .NullString = "0" 'Shows blank values as 0s
        
        'Add Detects page filter
            With .PivotFields("Detects")
                    .Orientation = xlPageField
                    .Position = 1
                    .PivotItems("ND").Visible = False 'Hide ND findings
                    .EnableMultiplePageItems = True 'Allow multiple selections
            End With
        'Add Group field
            With .PivotFields("Group")
                .Orientation = xlRowField
                .Caption = "Group"
                .ShowAllItems = True
            End With
            
          'Add Detects field and rename as "Count of Detects"
            With .PivotFields("Detects")
                .Orientation = xlDataField
                .Caption = "Count of Detects"
            End With
            
            With .PivotFields("Group")
                    .AutoSort xlDescending, "Count of Detects"
                    .PivotItems("SUM (PFOS + PFOA)").Visible = False 'Hide the sum of PFOS+ PFOA from the pivot table
            End With
            
             'Add the quarter data as a column
            With .PivotFields("Quarter")
                .Orientation = xlColumnField
                .Caption = "Quarters"
            End With
            
        End With
       
    End Sub
    PT2 Watch Window.png
    Attached Files Attached Files
    Last edited by paydog23; 03-20-2020 at 08:01 PM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Pivot table not created due to sheet reference
    By amartin575 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-25-2020, 12:56 PM
  2. [SOLVED] Changing Pivot Table Source- Code Created Files Require Manual Refresh Each Time Opened
    By Aquamore in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-08-2015, 05:53 PM
  3. Replies: 2
    Last Post: 09-03-2014, 10:51 AM
  4. VBA - Created Pivot Table - Filters Incorrectly
    By jhuang5132 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-13-2014, 05:02 PM
  5. Replies: 1
    Last Post: 06-05-2012, 04:52 PM
  6. VBA Created Pivot Table Doesn't Show Data
    By yawnzzzz in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-08-2008, 10:20 AM
  7. How to view VB Code behind Pivot Table created via Wizard
    By Firemark1817 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-20-2005, 12:06 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