我正在尝试从数据透视表创建柱形图。
我能够通过记录宏从我的代码生成数据透视表。
我尝试以同样的方式通过录制宏来生成图表,但这令人困惑。
所以,我尝试了自己的代码,我没有成功。谁能建议我如何从数据透视表生成图表,
下面是用于生成数据透视表的代码
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
下面是代码,我用于从数据透视表生成图表。
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
为了设置ChartObject.Chart.SetSourceData
,您需要将其设置为所需PivotTable
的TableRange2
。
另外,由于这是一个新sub
,它无法识别您在Sub AutoPivot
中Set
的局部变量PvtTbl
,因此您需要在此Sub
中再次Set
它。
最后,没有图表类型 xlColumn
,您需要从可用的有效选项中选择一个,成功为 xlColumnClustered
或 xlColumnStacked
等。
法典
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