VBA array as Chart source data

[BaGRoS]

My code is clumsy because I am searching, testing....
I'd like to use a two-dimensional array filled with data from several spreadsheets and give it as a data source for a chart. I cannot achieve this in any way.
ReDim Preserve always gives me an error, no matter how I use the function. I need several thousand rows and only two columns.

In general, I want to achieve what is in the link:

VBA filling the chart with data from multiple sheets

Another problem with VBA and charts.... I need to populate a chart with data from multiple worksheets, only where the InRange value is True. I don't want several different overlapping data, the result should be summed, as if in one sequence. So for example 5 sheets each with 1000 cells, there...

www.mrexcel.com

using an array is probably the easiest solution.

    Dim arr() As Variant, cell As Range, i%
    
    Dim objChrt As ChartObject
    Dim chrt As Chart
    Dim StartVal_X As String, EndVal_X As String, SheetName As String
    Dim StartVal_Y As String, EndVal_Y As String

    With Sheets("Graph_Template")
    .Activate
    
        'Scatter graph of weight ratios
        Set objChrt = .ChartObjects("Chart 3")
        objChrt.Activate
        Set chrt = objChrt.Chart

        i = 0
        ReDim arr(20000, 2)
        
        For Each cell In Sheets("Family Pack 01.11").Range("D2" & ":" & "D3699")
        
        i = i + 1
        'ReDim Preserve arr(UBound(arr, 1) + 1, 1 To 2)
        arr(i, 1) = cell.Value
        'Debug.Print cell.Value
        
        Next
        'Debug.Print arr()
        Dim rData As Range
        rData.Value = WorksheetFunction.Transpose(arr)
        
        chrt.SeriesCollection(1).Value = rData
        
        chrt.Refresh
        
        
        
    End With
    
    Debug.Print "Weekly"

 

[RoryA]

If you use Redim with Preserve, you can only alter the upper boundary of the last dimension. That means you need to transpose your array. You should also be aware that there will be a limit on the amount of data you can use since the literal values will be put into the SERIES formula for the chart, and that is limited in length. You are probably better off putting the data into a range somewhere and referring to that.

 

[BaGRoS]

Anyway, for now, I don't know how to give array as source data for the chart.
Step by step

 

[RoryA]

You would assign the array directly to the Value property of the series.

 

[BaGRoS]

Probably, I don't know how

 

[RoryA]

Sorry - it's Values not Value for a Series.

 

[BaGRoS]

Yes, this help a lot.

I tried used same for chart but a bit different:

and for this type of chart, not working.
I don't know how add array as data source.

I am increasingly considering copying all the data into a separate hidden sheet and this will probably be the best solution.
Later I will have to populate the data under the pivot table and again something will not work

 

[BaGRoS]

I change my mind...

    '******    START   ******
    Dim SheetNameStr As String
    Dim RowTable As Integer
    
    Sheets("HiddenTemp").rows("1:" & Worksheets("HiddenTemp").UsedRange.rows.Count).ClearContents
    Sheets("HiddenTemp").Range("A1") = "No."
    Sheets("HiddenTemp").Range("B1") = "TIME"
    Sheets("HiddenTemp").Range("C1") = "RANK"
    Sheets("HiddenTemp").Range("D1") = "WEIGHT"
        
    'First value
    RowTable = 88
    Do While Worksheets("Graph_Template").Range("K" & RowTable).Value <> ""
    
        If Worksheets("Graph_Template").Range("K" & RowTable).Value = "True" Then
    
            SheetsSource = Worksheets("Graph_Template").Range("H" & RowTable).Value
            rows = Worksheets(SheetsSource).UsedRange.rows.Count
               
            'MsgBox Worksheets("HiddenTemp").UsedRange.rows.Count + 1
            Worksheets("HiddenTemp").Range("B" & Worksheets("HiddenTemp").UsedRange.rows.Count + 1 & ":E" & Worksheets("HiddenTemp").UsedRange.rows.Count + rows - 1).Value _
               = Worksheets(SheetsSource).Range("B2:E" & rows).Value
           
            'MsgBox Worksheets("HiddenTemp").UsedRange.rows.Count
        
            'Debug.Print cell.Value
        
       End If
       
    RowTable = RowTable + 1
    Loop
       
       
    For i = 2 To Worksheets("HiddenTemp").UsedRange.rows.Count
        Worksheets("HiddenTemp").Range("A" & i).Value = i - 1
    Next
    
    'MsgBox Worksheets("HiddenTemp").UsedRange.rows.Count
    Worksheets("Graph_Template").Range("P18").Value = Worksheets("HiddenTemp").UsedRange.rows.Count

Now in HiddenTemp i have everything, should be easier. But last For Next is soooo slow?!