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 Whe=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
Whe=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

--
Message posted from http://www.ExcelForum.com

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

--
Message posted from http://www.ExcelForum.com

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

--
Message posted from http://www.ExcelForum.com

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

--
Message posted from http://www.ExcelForum.com

This code inserts chart onto already existing worksheet as object

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


This code creates new sheetchart


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

MiRa





MiRa


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

Thanx for your help anyway

Kind Regards


---
Message posted from http://www.ExcelForum.com/

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

Charts.Add
...
ActiveChart.Location Whe=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 he

http://peltiertech.com/Excel/ChartsH...kChartVBA.html

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

poppy < wrote:

Hi Experts

I trying to create a chart in one of my sheets by running a macro. The
macro runs fine and creates the chart, but keeps putting the chart into
the first sheet. It should actually be placed as an object in the sheet
where the source data is found. This is the code generated by the
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 Whe=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.Location
Whe=xlLocationAsObject, Name:="Brands by Brand"" indicates where the
chart should be stored?

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

Any help you can provide would be highly appreciated.

Kind Regards


---
Message posted from http://www.ExcelForum.com/