VBA在一张图纸上放置两个图表

Question

如何在一页上放置两张不同的图纸。我用这个代码创建了一个图表

'Create a chart
Set cht = Worksheets("Dashboard").Shapes.AddChart2
'Set cht = ActiveSheet.Shapes.AddChart2
With cht
.Chart.SetSourceData Source:=rng 'Give chart some data
.Chart.ChartType = xlColumnStacked 'Determine the chart type
.Chart.PlotBy = xlColumns
.ScaleHeight 1.7, msoFalse
.Chart.SetElement (msoElementLegendRight)
.Chart.ChartTitle.Text = Range("E1")
End With
Worksheets("Dashboard").Select
ActiveSheet.ChartObjects(1).Name = "FalseCall"
Range("A1") = ActiveSheet.ChartObjects(1).Name
ActiveSheet.Shapes("FalseCall").Left = Range("A1").Left
ActiveSheet.Shapes("FalseCall").Top = Range("A1").Top
ActiveSheet.Shapes("FalseCall").Width = Range("A1:G1").Width
ActiveSheet.Shapes("FalseCall").Height = Range("A1:A26").Height

它将在表"上添加一个图表；仪表板"；。

下一步，我想在H1的同一页面上添加一个具有其他名称的新图表

ActiveSheet.ChartObjects(1).Name = "Ng"

现有图表将用第二个图表的名称重命名并移动到H1，而不是新图表获得该名称并移动H1

Answer 1

这并不比用指定的新范围重复这些步骤更复杂。

Sub InsertTwoCharts()
Dim ws As Worksheet
Set ws = Worksheets("Dashboard")

' CHART 1
' range that chart will cover
Dim ChtRng1 As Range
Set ChtRng1 = ws.Range("A1:G26")

' chart data range
Dim ChtData1 As Range
Set ChtData1 = Worksheets("Whatever").Range("A1:E12") ' or whatever

' insert and modify the chart
Dim cht1 As ChartObject
Set cht1 = ws.Shapes.AddChart2
With cht1
.Name = "FalseCall"
.Chart.SetSourceData Source:=ChtData1, PlotBy:=xlColumns
.Chart.ChartType = xlColumnStacked
.Chart.SetElement msoElementLegendRight
.Chart.ChartTitle.Text = ws.Range("E1").Value ' pick a worksheet and cell
.Left = ChtRng1.Left
.Top = ChtRng1.Top
.Width = ChtRng1.Width
.Height = ChtRng1.Height
End With

' CHART 2
' range that chart will cover
Dim ChtRng2 As Range
Set ChtRng2 = ws.Range("H1:N26")

' chart data range
Dim ChtData2 As Range
Set ChtData2 = Worksheets("Whatever").Range("G1:K12") ' or whatever

' insert and modify the chart
Dim cht2 As ChartObject
Set cht2 = ws.Shapes.AddChart2
With cht2
.Name = "TrueCall"
.Chart.SetSourceData Source:=ChtData1, PlotBy:=xlColumns
.Chart.ChartType = xlColumnStacked
.Chart.SetElement msoElementLegendRight
.Chart.ChartTitle.Text = ws.Range("H1").Value ' pick a worksheet and cell
.Left = ChtRng2.Left
.Top = ChtRng2.Top
.Width = ChtRng2.Width
.Height = ChtRng2.Height
End With
End Sub