VBA - Source data from multiple sheets

Question

I am attempting to create a new chart using source data from multiple sheets. The data will always be in the same column no matter the sheet. However, I would like to select the data from Range("F2", Range("F2").End(xlDown)) from each of the sheets following the permanent two sheets at the beginning.

The sheets that I want data extracted from will be used as source data for a chart that will be created and deleted as new sheets are added, and the cycle will be repeated.

So, for example, I would like it to grab data from sheets 3 to 52, or however many there are, from the range specified above so it becomes one cohesive source. I am not sure if there is a nice way to do this without using a hidden sheet. Thanks in advance.

Private Sub Locations()
Set Build = Charts.Add(After = Worksheets("Report"))
    With Build
        .SetSourceData
        .ChartType = xlColumnClustered
    End With
End Sub

Answer 1

I was able to simplify the situation by using a hidden sheet

Private Sub Locations()

Dim ws As Worksheet, rep As Worksheet, LastRow As Double
With ThisWorkbook
    For n = 1 To Sheets.Count
        Set ws = Worksheets(n)
        Set rep = Worksheets("Report")
        LastRow = rep.Range("C1", rep.Range("C1").End(xlDown)).Rows.Count
        If IsNumeric(ws.Name) Then
            If rep.Range("C1") = "" Then
                ws.Range("F2", ws.Range("F2").End(xlDown)).Copy _
                Destination:=rep.Range("C1")
            Else:
                ws.Range("F2", ws.Range("F2").End(xlDown)).Copy _
                Destination:=rep.Range("C" & LastRow)
            End If
        End If
    Next n
End With

End Sub