Change source data to all sheets

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top