Dynamic Charts

This code creates Dynamic Charts in VBA. It needs a data source called rngRowSourse2 passed to it to allow it to build the correct chart. This code is also set to create pie charts by default but this can be changed.

Data to be passed can be collected from a sheet e.g. a data dump page from a database

Range(Range("IV1").End(xlToLeft), Range("B65536").End(xlUp)).Name = "rngRowSource2"

Then the following code builds the chart based on the range collected above

'add the chart to MTH tab built on range above Charts.Add 'Select Pie Chart ActiveChart.ChartType = xlPie 'Data from range defined above ActiveChart.SetSourceData Source:=rngRowSource2, PlotBy:=xlColumns 'put onto the MTH Page ActiveChart.Location Where:=xlLocationAsObject, Name:="MTH" 'Set Title With ActiveChart .HasTitle = True .ChartTitle.Characters.Text = "Outcome - All Demand Types" End With 'Switch On Legend & move to the bottom ActiveChart.HasLegend = True ActiveChart.Legend.Select Selection.Position = xlBottom 'Select the chart and remove default gray background and border ActiveChart.PlotArea.Select With Selection.Border .Weight = xlThin .LineStyle = xlNone End With With Selection.Interior .ColorIndex = 2 .PatternColorIndex = 1 .Pattern = xlSolid End With 'switch on % data lables ActiveChart.ApplyDataLabels AutoText:=True, LegendKey:=False, HasLeaderLines:=True, ShowSeriesName:=False, ShowCategoryName:=False, ShowValue:=False, ShowPercentage:=True, ShowBubbleSize:=False Range("A1").Select

Code Snippits