Microsoft Office Tutorials and References
In Depth Information
Moving a Chart
wherever VBA decides. That is why the code in the With structure specifies that cell C3 be the
topleft corner of the relocated chart.
Sub ChartSheetToWorksheet()
‘Chart1 is the name of the chart sheet.
Sheets(“Chart1”).Select
‘Move the chart to Sheet1
ActiveChart.Location Where:=xlLocationAsObject, Name:=”Sheet1”
‘Cell C3 is the top left corner location of the chart.
With Worksheets(“Sheet1”)
ActiveChart.Parent.Left = .Range(“C3”).Left
ActiveChart.Parent.Top = .Range(“C3”).Top
End With
‘Deselect the chart.
Range(“A1”).Select
End Sub
To move a chart from a worksheet to a chart sheet, you need to determine the name or index
number of your chart. If you have only one chart on the sheet, you know that chart’s index property is
1, but specifying the chart by its name is a safe way to go. The code is much simpler because a chart
sheet can contain only one chart, so you don’t need to specify a location on the chart sheet itself.
Sub EmbeddedChartToChartSheet()
ActiveSheet.ChartObjects(“Chart 1”).Activate
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:=”Chart1”
End Sub
To determine the name of any embedded chart quickly, select it and you’ll see its
name in the Name box.
To move an embedded chart from one worksheet to another, it’s the same concept of specifying
which chart to move, and which worksheet to move it to:
Sub EmbeddedChartToAnotherWorksheet()
‘Chart 5 is the name of the chart to move to Sheet2.
ActiveSheet.ChartObjects(“Chart 14”).Activate
ActiveChart.Location Where:=xlLocationAsObject, Name:=”Sheet2”
‘Cell B6 is the top left corner location of the chart.
With Worksheets(“Sheet2”)
ActiveChart.Parent.Left = .Range(“B6”).Left
ActiveChart.Parent.Top = .Range(“B6”).Top
End With
‘Deselect the chart.
Range(“A1”).Select
End Sub
 
Search JabSto ::




Custom Search