Charts and Macros

Hello!

I created a spreadsheet, which actually is a report; I didn't write the macro, but recorded it. In the recording, I selected the data, then hit the charts Icon on the tool bar. While still recording, if I try to move the chart to another location on the same sheet, the macro returns an error. But if I record it without moving the chart, then the macro runs fine.

Q. Is it possible to run a macro, which creates a graph and positions it in another location on the sheet?

Thanks

I recorded the macro by moving the chart in same sheet, I did not get any error.

What is the error that you got.


recorded macro is

Range("D9:E13").Select
Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("D9:E13"), PlotBy _
:=xlColumns
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
ActiveChart.SeriesCollection(2).Select
ActiveChart.ChartArea.Select
ActiveSheet.Shapes("Chart 1").IncrementLeft 81#
ActiveSheet.Shapes("Chart 1").IncrementTop 190.5
ActiveSheet.Shapes("Chart 1").IncrementLeft -3.75
ActiveSheet.Shapes("Chart 1").IncrementTop -56.25
ActiveSheet.Shapes("Chart 1").IncrementLeft 21.75
ActiveSheet.Shapes("Chart 1").IncrementTop -40.5
ActiveSheet.Shapes("Chart 1").IncrementLeft -228#
ActiveSheet.Shapes("Chart 1").IncrementTop 38.25

Thanks Anilsolipuram for the reply;

I tried it again, and got:

Run - time error '-2147024809 (80070057)';

This item with the specified name wasn't found

I hit debug, and excel highlighted in yellow: Active Sheet.Shapes ("Chart 2"). Increment left -174.75


I'm clueless???????


Thanks

Also Anilsolipuram, what I'm doing is grabbing the chart with the mouse pointer and dragging it to the top left corner of the sheet.

Maybe this is not right. should I move it somehow with arrow keys??????

Thanks again.

try this macro.

The data for chart should be in a1:d5 in sheet1



Sub Macro2()

Range("A1:D5").Select
Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:D5"), PlotBy:= _
xlColumns
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Percentage"
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
Dim var, ch_name As String
var = ActiveChart.Name
t = Split(var, ActiveSheet.Name)
ch_name = t(1)

ActiveSheet.Shapes(ch_name).IncrementLeft -186.75
ActiveSheet.Shapes(ch_name).IncrementTop -108.75
ActiveSheet.Shapes(ch_name).ScaleHeight 1.35, msoFalse, msoScaleFromTopLeft
ActiveSheet.Shapes(ch_name).ScaleWidth 1.12, msoFalse, msoScaleFromTopLeft
ActiveWindow.Visible = False
Windows("Book1").Activate
Range("J9").Select
End Sub