harsha547
6/7/2017 - 5:25 PM

Pivot Table VBA

Pivot Table VBA

private sub createpivot()

   On Error Goto EH:
   
   Dim ptCache As PivotCache
   
   Set ptCache  = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase _ 
                          , SourceData:= chRoyaltyReport.ListObjects("tabRoyalty) _
                          , Version:=xlPivotTableVersion15)
   ' ChRoyaltyReport Worksheet name
   ' Listobjects tabroyalty is table name
   
   ' CnPivot is worksheet Name
   ' TABLE_DEST_RANGE is variable declared as CONST
                          
   Dim ptTable As PivotTable
   Set ptTable = cnPivot.PivotTables.Add(PivotCache:=ptcache _
                     , TableDestination:= cnPivot.Range( TABLE_DEST_RANGE ))
    
    'TABLE_NAME is declared as CONST Variable
    
    ptTable.Name = TABLE_NAME
    ptTable.TableStyle2 = "PivotStyleDark14"
    
    'xlRowField
    'xlColumnField
    'xlDataField
    'XlpageField
    
    
    Dim ptField As PivotField
    
    with ptTable
            .PivotFields("Year").Orientation = xlRowField
            .PivotFields("Date").Orientation = xlRowField
            .PivotFields("Title").Orientation = xlColumnField
            .PivotFields("Net Units Sold").Orientation = xlDataField
            .PivotFields("Royalty Amount").Orientation = xlDataField
            
            ' Filter
            .PivotFields("Market Place").Orientation = xlPageField
            .PivotFields("Market Place").CurrentPage = "Amazon.co.uk"
            
            ' Group By Week
            .PivotFields("TheDate").DataRange.Cells(2,1).Group by:=7 _
              , Periods:= Array(False , False , False , True , False , False , False )
              
              ' Array ( Seconds , Minutes , Hours , Days , Months , Quartely , Year )
            


    End With
    
    
    Done:
       Exit Sub
     
    Eh :
       MsgBox Err.Description & "BuildPivot.CreatePivot"
      
      ' SHIFT + F2
      ' CTRL + F4
End Sub