V
Vlad999
I have defined various name ranges and I use them in a macro to create
a chart it works fine for sheet 1. But i want to use the same dynamic
range for all sheets in the workbook.
So basically what i want is a macro to run through my workbook and
create a chart, for the data in the defined range, on every worksheet
(excluding the worksheet titled "Master").
Code:
--------------------
Sub addChart()
Dim myChart As Excel.Chart
'add chart
Set myChart = Charts.Add
'set chart type for e.g. xlPie,xlLine,xlArea,xl3DColumn etc
ActiveChart.ApplyCustomType ChartType:=xlUserDefined, TypeName:= _
"Cumulative or Comparative"
'set data range
myChart.SetSourceData Source:=Range("mediaeq"), PlotBy:=xlColumns
'Turn on the major gridlines for both axes
myChart.Axes(xlCategory).HasMajorGridlines = False
myChart.Axes(xlValue).HasMajorGridlines = False
ActiveChart.SeriesCollection(1).XValues = Range("programs")
ActiveChart.SeriesCollection(1).Values = Range("mediaeq")
'Turn on the Legend and position it on top of the chart
myChart.HasLegend = False
'Show values on the bars of the chart
myChart.ApplyDataLabels xlDataLabelsShowValue
'Finally, which sheet you want the chart on
myChart.Location xlLocationAsObject, "Sheet1" 'This adds a standard sized chart to sheet2, but if you want to add a
' new sheet with just chart on it, replace the above line with next line...
'myChart.Location xlLocationAsNewSheet, "Mychartsheet"
'If you want specify the height and width of the chart object.......
ActiveSheet.Shapes(ActiveChart.Parent.Name).ScaleWidth 0.7, msoFalse, msoScaleFromTopLeft
ActiveSheet.Shapes(ActiveChart.Parent.Name).ScaleHeight 0.7, msoFalse, msoScaleFromTopLeft
Set myChart = Nothing
End Sub
a chart it works fine for sheet 1. But i want to use the same dynamic
range for all sheets in the workbook.
So basically what i want is a macro to run through my workbook and
create a chart, for the data in the defined range, on every worksheet
(excluding the worksheet titled "Master").
Code:
--------------------
Sub addChart()
Dim myChart As Excel.Chart
'add chart
Set myChart = Charts.Add
'set chart type for e.g. xlPie,xlLine,xlArea,xl3DColumn etc
ActiveChart.ApplyCustomType ChartType:=xlUserDefined, TypeName:= _
"Cumulative or Comparative"
'set data range
myChart.SetSourceData Source:=Range("mediaeq"), PlotBy:=xlColumns
'Turn on the major gridlines for both axes
myChart.Axes(xlCategory).HasMajorGridlines = False
myChart.Axes(xlValue).HasMajorGridlines = False
ActiveChart.SeriesCollection(1).XValues = Range("programs")
ActiveChart.SeriesCollection(1).Values = Range("mediaeq")
'Turn on the Legend and position it on top of the chart
myChart.HasLegend = False
'Show values on the bars of the chart
myChart.ApplyDataLabels xlDataLabelsShowValue
'Finally, which sheet you want the chart on
myChart.Location xlLocationAsObject, "Sheet1" 'This adds a standard sized chart to sheet2, but if you want to add a
' new sheet with just chart on it, replace the above line with next line...
'myChart.Location xlLocationAsNewSheet, "Mychartsheet"
'If you want specify the height and width of the chart object.......
ActiveSheet.Shapes(ActiveChart.Parent.Name).ScaleWidth 0.7, msoFalse, msoScaleFromTopLeft
ActiveSheet.Shapes(ActiveChart.Parent.Name).ScaleHeight 0.7, msoFalse, msoScaleFromTopLeft
Set myChart = Nothing
End Sub