Creating a dynamic charting macro in VB

Question

Currently I'm trying to design a macro which will allow me to select data from multiple worksheets and based off values picked up, chart them against each other. The cell holding the value will be the same across every other worksheet.

I wrote down some pseudo-attempts regarding how I would approach this:

Function WorksheetExists(ByVal WorksheetName As String) As Boolean
Dim Sht As Worksheet
Dim Value = "B20" 'The value to chart will be B20 across every chart'
For each Sheet in ThisWorkbook.Worksheets
'This is where I would start referencing the B20 fields and comparing them'

End Function

Thanks!

Answer 1

Following code is using another approach, instead of selecting multiple cells from different sheets it copies cell content from each sheet to another sheet called "Graph" and selects that range as your graph source. Code assumes that "Graph" sheet is the first sheet of your workbook. This may not be the perfect answer for your question however you can do some modifications to achieve your goal.

Sub GraphAdd()

Dim sourceRng As Range
Dim sht As Worksheet
Dim graphSht As Worksheet
Dim wb As Workbook
Dim lastRow As Long

Set wb = ActiveWorkbook
Set graphSht = wb.Worksheets("Graph") '<-- Sheet name where you want to draw the chart

i = 1
For Each sht In ActiveWorkbook.Sheets
    graphSht.Cells(i, 1).Value = sht.Range("B20").Value
    i = i + 1
Next

lastRow = graphSht.Range("A" & graphSht.Rows.Count).End(xlUp).Row

    graphSht.Shapes.AddChart.Select
    ActiveChart.ChartType = xlColumnClustered
    ActiveChart.SetSourceData Source:=graphSht.Range("A2:A" & lastRow)

End Sub