Macro to create a new chart
 

I've been using this code for a while to easily create a new chart.
However just recently has stopped working. It now created a "default"
excel chart as a new tab in the workbook. Any suggestions?

'************************************************* *******************
'Creates 2 data series horizontal bar chart
'************************************************* *******************

Dim myShtName As String
myShtName = ActiveSheet.Name
Charts.Add
ActiveChart.ChartType = xlBarClustered 'Creates Stacked bar
ActiveChart.SetSourceData Source:=Rng, PlotBy:=xlColumns
'References sheet and and cell range of data to generate chart
ActiveChart.Location Whe=xlLocationAsObject, Name:=myShtName
With ActiveChart.Axes(xlCategory)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
With ActiveChart.Axes(xlValue)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
ActiveChart.HasLegend = True

'************************************************* *******************
'Specifies placement and height & width dimensions of chart
'************************************************* *******************

With ActiveChart.Parent
.Top = ActiveSheet.Range("F22:Q22").Top
.Left = ActiveSheet.Range("F22:Q52").Left
.Height = ActiveSheet.Range("F22:Q52").Height
.Width = ActiveSheet.Range("F22:Q22").Width
End With

Macro to create a new chart
 

You'll save a lot of grief if you make the chart directly in the worksheet,
using the ChartObjects.Add method.

Dim wksMySheet As Worksheet

Dim chtMyChart As Chart

Dim dTop as Double

Dim dLeft as Double

Dim dHeight as Double

Dim dWidth as Double

Set wksMySheet = ActiveSheet

dTop = wksMySheet .Range("F22:Q22").Top

dLeft = wksMySheet .Range("F22:Q52").Left

dHeight = wksMySheet .Range("F22:Q52").Height

dWidth = wksMySheet .Range("F22:Q22").Width

Set chtMyChart = wksMySheet.ChartObjects.Add(dLeft, dTop, dWidth,
dHeight).Chart

With chtMyChart

.ChartType = xlBarClustered 'Creates Stacked bar

.SetSourceData Source:=Rng, PlotBy:=xlColumns

With .Axes(xlCategory)

.HasMajorGridlines = False

.HasMinorGridlines = False

End With

With .Axes(xlValue)

.HasMajorGridlines = False

.HasMinorGridlines = False

End With

.HasLegend = True

End With


More VBA charting tips:

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

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


"kimbobo" wrote in message
oups.com...
I've been using this code for a while to easily create a new chart.
However just recently has stopped working. It now created a "default"
excel chart as a new tab in the workbook. Any suggestions?

'************************************************* *******************
'Creates 2 data series horizontal bar chart
'************************************************* *******************

Dim myShtName As String
myShtName = ActiveSheet.Name
Charts.Add
ActiveChart.ChartType = xlBarClustered 'Creates Stacked bar
ActiveChart.SetSourceData Source:=Rng, PlotBy:=xlColumns
'References sheet and and cell range of data to generate chart
ActiveChart.Location Whe=xlLocationAsObject, Name:=myShtName
With ActiveChart.Axes(xlCategory)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
With ActiveChart.Axes(xlValue)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
ActiveChart.HasLegend = True

'************************************************* *******************
'Specifies placement and height & width dimensions of chart
'************************************************* *******************

With ActiveChart.Parent
.Top = ActiveSheet.Range("F22:Q22").Top
.Left = ActiveSheet.Range("F22:Q52").Left
.Height = ActiveSheet.Range("F22:Q52").Height
.Width = ActiveSheet.Range("F22:Q22").Width
End With