Excel 97 macro copies data blocks to a sheet and plots a graph. This
works well but the size and position is somwhat random. I would like
it to appear below the data array and be about the same size. Can that
be done?

Charts.Add
ActiveChart.ChartType = xlSurface
ActiveChart.SetSourceData Source:=Sheets("Test").Range("E" &
MapPasteTo + 27 & ":Q" & MapPasteTo + 39), _
PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsObject, Name:="Test"

The following Methods can be used to modify the dimensions and position of a
ChartObject:
Height
Width
Top
Left

--
If a post answers your question, please mark it as the answer.


"Fan924" wrote:

> Excel 97 macro copies data blocks to a sheet and plots a graph. This
> works well but the size and position is somwhat random. I would like
> it to appear below the data array and be about the same size. Can that
> be done?
>
> Charts.Add
> ActiveChart.ChartType = xlSurface
> ActiveChart.SetSourceData Source:=Sheets("Test").Range("E" &
> MapPasteTo + 27 & ":Q" & MapPasteTo + 39), _
> PlotBy:=xlColumns
> ActiveChart.Location Where:=xlLocationAsObject, Name:="Test"
>
>

Sub ABC()
Dim ch As Chart
Charts.Add
MapPasteTo = 1
'Set r = Sheets("Test").Range("E" & _
MapPasteTo + 27 & ":Q" & MapPasteTo + 39)
ActiveChart.SetSourceData _
Source:=Sheets("Test").Range("E" & _
MapPasteTo + 27 & ":Q" & MapPasteTo + 39), _
PlotBy:=xlColumns
ActiveChart.ChartType = xlSurface
ActiveChart.Location Where:=xlLocationAsObject, Name:="Test"
Set ch = ActiveChart
With Worksheets("Test").Range("E" & MapPasteTo)
ch.Parent.Top = .Top
ch.Parent.Left = .Left
End With

End Sub

worked for me.

--
Regards,
Tom Ogilvy


"Fan924" wrote:

> Excel 97 macro copies data blocks to a sheet and plots a graph. This
> works well but the size and position is somwhat random. I would like
> it to appear below the data array and be about the same size. Can that
> be done?
>
> Charts.Add
> ActiveChart.ChartType = xlSurface
> ActiveChart.SetSourceData Source:=Sheets("Test").Range("E" &
> MapPasteTo + 27 & ":Q" & MapPasteTo + 39), _
> PlotBy:=xlColumns
> ActiveChart.Location Where:=xlLocationAsObject, Name:="Test"
>
>

Worked great Tom. Thanks. I added 40 to the line count so it appears
below the data and added width and height. Exactly what I wanted!
With Worksheets("Test").Range("E" & MapPasteTo + 40)
ch.Parent.Top = .Top
ch.Parent.Left = .Left
ch.Parent.Width = 600
ch.Parent.Height = 300