+ Reply to Thread
Results 1 to 2 of 2

VBA for pivot table won't group fields

Hybrid View

  1. #1
    Registered User
    Join Date
    11-07-2013
    Location
    Chicago
    MS-Off Ver
    Excel 2013
    Posts
    4

    VBA for pivot table won't group fields

    My macro creates a pivot table but I am running into an issue when it goes to group one of the numeric columns as I get a "Group method of Range class failed" message. The grouping portion of the code is from a recorder. The rest of the code not shown attempts to group three other fields and then finish out. Any thoughts on how to fix this?

    Also, I have to create this report weekly so it's a new one each week. Do you see any code issues with the creation of the table? I've noticed that the pivot table # changes each time it gets created so I'm wondering if I need to be more exact? Sorry if that second question is too vague. Thanks!


    Pivot table
    Dim PTCache As PivotCache
    Dim PT As PivotTable
    
    Set PTCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=Range("A1").CurrentRegion)
    
    'Adding new sheet for the pivot table
    Worksheets.Add
    
    'Creating the Pivot Table
    Set PT = ActiveSheet.PivotTables.Add(PivotCache:=PTCache, TableDestination:=Range("A3"))
    
    
     ActiveSheet.PivotTables("PivotTable1").RowAxisLayout xlTabularRow
        With ActiveSheet.PivotTables("PivotTable1")
            .ColumnGrand = False
            .RowGrand = False
        End With
        With ActiveSheet.PivotTables("PivotTable1").PivotFields("Grade 1")
            .Orientation = xlRowField
            .Position = 1
        End With
            
        Selection.Group Start:=True, End:=100, By:=15
        ActiveSheet.PivotTables("PivotTable1").PivotFields("Grade 1").Orientation = _
            xlHidden

  2. #2
    Registered User
    Join Date
    11-07-2013
    Location
    Chicago
    MS-Off Ver
    Excel 2013
    Posts
    4

    Re: VBA for pivot table won't group fields

    I was able to figure this out. Here is the code.

    Sub WeeklyReportStep8()
    'Pivot Table creation
    
    Dim PTCache As PivotCache
    Dim PT As PivotTable
    Dim PF As PivotField
    
    
    'Create the Cache
    Set PTCache = ActiveWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, _
        SourceData:=Range("A1").CurrentRegion)
        
    'Add a new sheet for the pivot table
    Worksheets.Add
    
    Application.ScreenUpdating = False
    
    'Create the pivot table
    Set PT = ActiveSheet.PivotTables.Add( _
        PivotCache:=PTCache, _
        TableDestination:=Range("A3"))
    
    PT.RowAxisLayout xlTabularRow
    PT.ShowDrillIndicators = False
    PT.ColumnGrand = False
    PT.RowGrand = False
    
    
    'specify fields to be grouped
    With PT
        .PivotFields("Grade 1").Orientation = xlRowField
    End With
    
    Dim rPTRange As Range
    Set PF = PT.RowFields("Grade 1")
    Set rPTRange = PF.DataRange.Cells(1, 1)
    rPTRange.Group Start:=True, End:=100, By:=15
    
    With PT
        .PivotFields("Grade 1").Orientation = xlHidden
    
    End Sub
    
    End With

+ 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. Collapse/Expand - Pivot table Fields - Need equivalent option in Excel VBA Pivot table
    By ragavendraph in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-07-2012, 03:00 PM
  2. Replies: 1
    Last Post: 04-25-2011, 01:17 PM
  3. Hiding Specific Fields on Pivot Table Fields
    By branco in forum Excel General
    Replies: 7
    Last Post: 03-26-2010, 09:39 AM
  4. How to Disable automatic fields in pivot table like total and count on fields
    By anushka in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 09-24-2009, 07:53 AM
  5. Differentiate between column fields and data fields in a pivot table
    By whiteheadw in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-12-2009, 01:59 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