Generate column chart from updated results

Question

The following code, find the appropriate row based on today's date (the date is already written manually in column A) and insert the data on the same row. I need to add the last result( today's date )to a column chart on a different sheet called "charts".

Sub Worksheets_Summary()
Dim OldSheet As Worksheet
Dim NewSheet As Worksheet
Dim Cell As Range
Dim ColNum As Integer
Dim RwNum As Long
Dim book As Workbook
Set book = ThisWorkbook
Set NewSheet = book.Worksheets("Summary")
RwNum = NewSheet.Columns(1).Find(Date).Row
ColNum = 1
For Each OldSheet In book.Worksheets
    If OldSheet.Name <> "Summary" Then
        ColNum = ColNum + 1

        NewSheet.Cells(1, ColNum).Formula _
    = "=HYPERLINK(""#""&CELL(""address"",'" & OldSheet.Name & "'!A1)," _
         & """" & OldSheet.Name & """)"


        NewSheet.Cells(RwNum, ColNum).Value = OldSheet.Range("B11").Value
    End If
Next OldSheet

NewSheet.UsedRange.Columns.AutoFit

End Sub

Note: Only the new results are shown in the chart.

This is the Summary worksheet after the code has run: Summary sheet

This is the Dashboard worksheet after the code has run: Dashboard Sheet

Answer 1

Want a non-VBA method, that once you've set it up you'll never have to mess with it?

We'll use some Names (aka Named Ranges) to reference the data.

I assume the sheet with the data you're plotting is "Summary".

Go to the Formulas tab, Define Name. In Name, enter Labels; for Scope, keep Workbook; in Refers To, enter =Summary!$B$1:$E$1. Click Enter.

Return to Define Name. In Name, enter Name; in Refers To, enter =OFFSET(Summary!$A$1,COUNT(Summary!$A:$A),0). This returns the cell that is as many rows below A1 as there are values in column A.

Return one last time to Define Name. In Name, enter Values; in Refers To, enter =OFFSET(Labels,COUNT(Summary!$A:$A),0). This returns the range which is as many rows below the Labels range we named above, as the number of values in column A.

Now select Summary!A1:D2 and insert a column chart on the Summary sheet. The chart shows the first date's values. Select the columns and look at the series formula. It should say this:

=SERIES(Summary!$A$2,Summary!$B$1:$D$1,Summary!$B$2:$D$2,1)

Edit this formula to read

=SERIES(Summary!Name,Summary!Labels,Summary!Values,1)

Excel accepts your changes. Since the scope of the Names was Workbook, Excel changes the series formula to this:

=SERIES(Book1.xlsm!Name,Book1.xlsm!Labels,Book1.xlsm!Values,1)

(or whatever your workbook's name is).