Using VBA to create/update a graph from a particular cell across lots of sheets.

[DKrakken]

I've been creating a sheet for work to help me out in my job but i don't know VBA very well at all and have been trying to work things out via google etc. I've managed to get quite far but i've ran into an issue and i don't know where to begin.

So i currently have a "home" sheet where i want it to have a graph that when the user presses "Update statistics" it runs through all of the sheets and updates a graph with the values from a specific cell(essentially a total of available items). The idea of this is that i can see the daily change of items available. Each new data point will be the name of the sheet (the date). The final added point is that the first 3 sheets never change however there is a button in my home page that creates a new sheet, similar to a form template where people fill in for each day. So after my 3rd sheet i want the information from every sheet in this graph. Any ideas on where to start? Any more information needed? Hopefully i've explained it well enough.

Thanks for any future help!

 

[DKrakken]

Well i've managed to get somewhere since then. I've managed to get Excel to create a table on a separate sheet currently named "sheet 1" using the below code. It also calls for a run of a 2nd macro which is almost identical but to populate the 2nd column of the table. This code does all sheets, i would like it to skip the first 4, is there an easy way to do that? I can't seem to work it out.

Sub copycelldate()
    Dim sh As Worksheet
    Dim wb As Workbook
    Dim DestSh As Worksheet
    Dim i As Integer

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    Set wb = ThisWorkbook
    Set DestSh = wb.Sheets("Sheet1")

    ' Loop through worksheets that start with the name "20"
    i = 2
    For Each sh In ActiveWorkbook.Worksheets
    If sh.Name = "END" Then Call copycelltotal
        sh.Range("AD1").Copy
        With DestSh.Range("A" & i)
            .PasteSpecial xlPasteValues
            .PasteSpecial xlPasteFormats
            Application.CutCopyMode = False
        End With
    i = i + 1

    Next
    
End Sub

Sub CreateChart()

Dim WS As Worksheet

Set WS = Worksheets("Sheet1")

WS.Activate

WS.Range("A6:B50").Select

WS.Shapes.AddChart2(227, xlLine).Select

End Sub

Additionally to this, i'm now creating the line graph off this information however im trying to add a dynamic range to both ranges as the users will regularly add sheets and i want it to update with the new information. The code i have so far is below:

Sub CreateChart()

Dim WS As Worksheet

Set WS = Worksheets("Sheet1")

WS.Activate

WS.Range("A6:B50").Select

WS.Shapes.AddChart2(227, xlLine).Select

End Sub

How do i get this to be a dynamic range? I've tried a few things but they don't seem to work.

Cheers again.