CODEWITHSUNDEEP
vbaexcelcharts
Mikz
Mikz

Reputation: 591

Create Column Chart from Pivot table

I am trying to create Column Chart from Pivot table.

I am able to generate the Pivot table from my code by recording the macros.

I tried in the same way to generate the Chart by recording the macros, but it was confusing.

So, I tried my own code, I am not successful. Could anyone suggest how i could generate an Chart from Pivot table,

Below is the code used for generating the Pivot table 

Sub AutoPivot()

Dim PvtCache            As PivotCache
Dim PvtTbl              As PivotTable
Dim pvtsht              As Worksheet

' set Pivot Cache for Pivot Table
' Your range is static, there are ways to refer to a dynamic range
Set PvtCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="Preparation sheet!R1C1:R1048576C8")

' set the Pivot table's sheet
Set pvtsht = Worksheets("CAT_Pivot")


' add this line in case the Pivot table doesn't exit >> first time running this Macro
On Error Resume Next
Set PvtTbl = pvtsht.PivotTables("PivotTable1") ' check if "PivotTable7" Pivot Table already created (in past runs of this Macro)

On Error GoTo 0
If PvtTbl Is Nothing Then ' Pivot table object is nothing >> create it            
    ' create a new Pivot Table in "PivotTable4" sheet
    Set PvtTbl = pvtsht.PivotTables.Add(PivotCache:=PvtCache, TableDestination:=pvtsht.Range("A3"), TableName:="PivotTable1")

    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
    "PivotTable1").PivotFields("Colour"), "Count of Colour", xlCount

      With PvtTbl.PivotFields("Category")
          .Orientation = xlRowField
          .Position = 1
      End With
      With PvtTbl.PivotFields("Colour")
          .Orientation = xlColumnField
          .Position = 1
      End With
      With PvtTbl.PivotFields("Category")
          .PivotItems("DG").Visible = False
          .PivotItems("DG").Visible = False
          .PivotItems("DG-Series").Visible = False
          .PivotItems("gn").Visible = False
          .PivotItems("yl").Visible = False
          .PivotItems("(blank)").Visible = False
      End With
      With PvtTbl.PivotFields("Colour")
          .PivotItems("(blank)").Visible = False
      End With
Else
     ' just refresh the Pivot cache with the updated Range
     PvtTbl.ChangePivotCache PvtCache
     PvtTbl.RefreshTable        
End If

End Sub

Below is the code, i used for generating the Chart from Pivot table. 

Sub Autochart()

    Dim chobj As ChartObject        
    Dim ch As Chart        
    Dim pvtsht As Worksheet

    Set pvtsht = Sheets("CAT_Pivot")        
    Set chobj = pvtsht.ChartObjects.Add(300, 200, 550, 200)        
    '300 left, 220 top, 550 width, 200 height

    Set ch = chobj.Chart        
    ch.SetSourceData pt.PvtTbl        
    ch.ChartType = xlColumn        
    chobj.Name = "EChart1"

End Sub

Upvotes: 1

Views: 2070

Answers (1)

Shai Rado
Shai Rado

Reputation: 33692

In order to set the ChartObject.Chart.SetSourceData, you need to set it to the TableRange2 of your desired PivotTable.

Also, since this is a new sub, it doesn't recognize the local variable PvtTbl you have Set in Sub AutoPivot, so you need to Set it again in this Sub.

Last, there is no chart type xlColumn, you need to select one from the available valid options, suce as xlColumnClustered or xlColumnStacked, etc.

Code

Sub Autochart()

Dim chtObj      As ChartObject
Dim PvtSht      As Worksheet
Dim PvtTbl      As PivotTable

' set the Pivot sheet
Set PvtSht = Sheets("CAT_Pivot")

' set the Pivot Table object
Set PvtTbl = PvtSht.PivotTables("PivotTable1")

' set the Chart Object
Set chtObj = PvtSht.ChartObjects.Add(300, 200, 550, 200)

' modify ChartObject properties
With chtObj
    .Chart.SetSourceData PvtTbl.TableRange2 ' set the chart's data range to the Pivot-Table's TableRange2
    .Chart.ChartType = xlColumnClustered
    .Name = "EChart1"
End With

End Sub

Upvotes: 2

Related Questions