Chart is being dumped into the wrong sheet

[poppy]

Hi Experts

I trying to create a chart in one of my sheets by running a macro. Th
macro runs fine and creates the chart, but keeps putting the chart int
the first sheet. It should actually be placed as an object in the shee
where the source data is found. This is the code generated by th
macro:


Code
-------------------
Range("A14:H16").Select
Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheets("Brands by Brand").Range("A14:H16") _
, PlotBy:=xlRows
ActiveChart.Location Where:=xlLocationAsObject, Name:="Brands by Brand"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Qty of Tyres Sold"
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
ActiveChart.HasLegend = True
ActiveChart.Legend.Select
Selection.Position = xlBottom
ActiveSheet.Shapes("Chart 1").IncrementLeft -84.75
ActiveSheet.Shapes("Chart 1").IncrementTop 131.25
ActiveChart.SeriesCollection(1).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.Shadow = False
Selection.InvertIfNegative = False
With Selection.Interior
.ColorIndex = 5
.Pattern = xlSolid
End With
ActiveChart.SeriesCollection(2).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.Shadow = False
Selection.InvertIfNegative = False
With Selection.Interior
.ColorIndex = 19
.Pattern = xlSolid
End With

-------------------


Am I right in assuming that this line: "ActiveChart.Locatio
Where:=xlLocationAsObject, Name:="Brands by Brand"" indicates where th
chart should be stored?

On the other hand when I run the macro and create the graph, the las
step is to decide if it should be stored as an object in the curren
sheet or dumped into it's own sheet. This procedure is not being picke
up so the chart ends up being dumped into the first sheet.

Any help you can provide would be highly appreciated.

Kind Regard

 

[mangesh_yadav]

Well, there doesn't seem to be anything wrong with your code. But th
chart will be added as a new chartsheet in case the sheetname you giv
in the code (i.e. Brands by brand) does not exist (one reason is wron
spelling). Please check this.

- Manges

 

[poppy]

Hi mangesh

I have triple checked my worksheet name and there is nothing wrong wit
the spelling and I have also tried to just cut the graph and put it i
to the correct sheet but it just gives me an error - this from the ru
macro.

Thanx for your help anyway

Kind Regard

 

[mangesh_yadav]

And what is the name given to the new chart object where the graph i
placed, or if it is put in another sheet, then what is the name of thi
sheet...?

- Manges

 

[MiRa]

This code inserts chart onto already existing worksheet as object

ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet2"


This code creates new sheetchart


ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="NewSheet"

MiRa





MiRa

 

[Jon Peltier]

I prefer to add the chart directly to the worksheet. Instead of

Charts.Add
...
ActiveChart.Location Where:=xlLocationAsObject, Name:="SomeSheet"

I use this

Worksheets("SomeSheet").ChartObjects.Add _
Top:=100, Left:=100, Height:=225, Width:=375

The dimensions are in points. Specifying size and dimensions here
prevent you from having to move and scale the chart in the macro.

There's more about charts and VBA here:

http://peltiertech.com/Excel/ChartsHowTo/QuickChartVBA.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______