VBA filling the chart with data from multiple sheets

[BaGRoS]

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 should be 5000 values on the chart.

WeeklyReport table:

This probably not working:

chrt.SetSourceData (Sheets(SheetName).Range(StartVal_X & ":" & EndVal_X))

 

[Jon Peltier]

It's not clear what you want.

A single chart series gets its X values only from one sheet, and its Y values only from one sheet (they may be different sheets). If you have X and Y values from different sheet, you must either consolidate the data into one sheet, or make a separate series in the chart for each data sheet.

 

[BaGRoS]

It's not clear what you want.

A single chart series gets its X values only from one sheet, and its Y values only from one sheet (they may be different sheets). If you have X and Y values from different sheet, you must either consolidate the data into one sheet, or make a separate series in the chart for each data sheet.

Practically everything works for me.

Only the Histogram-type chart remains.

For nothing in the world can I get it to work as a dynamic one.

I have a hidden sheet with a varying number of rows.

No matter how I substitute the data into the Histogram-type chart, they are always stored permanently, e.g. if I enter the data through the Name Manager, it works only at the moment of entry, if, for example, it was 100 rows, then even when later there are 1000 rows, the Histogram chart shows the last 100.

I use "as dynamic"

and later always stay like this:

even if is 20000 rows

I know this is to do with VBA but my skills are not enough.

 

[Jon Peltier]

Whenever you enter a range name into the Chart Data Range box, Excel accepts the range, but always converts it to the range address. For any chart type.

Convert the data to a Table, and those cell addresses will adjust when the Table changes size.

 

[BaGRoS]

Whenever you enter a range name into the Chart Data Range box, Excel accepts the range, but always converts it to the range address. For any chart type.

Convert the data to a Table, and those cell addresses will adjust when the Table changes size.

I converted to the table:

CellName = "A1:E" & Worksheets("HiddenTemp").UsedRange.rows.Count
  
    Set cell = Worksheets("HiddenTemp").Range(CellName)
    Worksheets("HiddenTemp").ListObjects.Add(SourceType:=xlSrcRange, Source:=cell, _
        xlListObjectHasHeaders:=xlYes, tablestyleName:="TableStyleMedium28").Name = "HiddenTab"

after enter is changed to:

and still is not dynamic

 

[Jon Peltier]

Your chart has to include the entire column of the table for the cell address to remain dynamic. Your screenshot of the Table shows D2 as the first data cell, but your screenshot of the Chart Data Range shows D3 as the first data cell.

Try it manually, not with VBA.

 

[BaGRoS]

Your chart has to include the entire column of the table for the cell address to remain dynamic. Your screenshot of the Table shows D2 as the first data cell, but your screenshot of the Chart Data Range shows D3 as the first data cell.

Try it manually, not with VBA.

Second picture is when I press ENTER after the first picture. Office change like this. I don't know why is D3, usual is D2

1910 packs

ENTER, and:

after product change:

20466 packs, but table:

In this time, table:

 

[Jon Peltier]

Changing HiddenTab[Weight] to HiddenTemp!$D$2:$D$1911 is normal behavior, which I described before.

Your Table's data range in your screenshot does not stop at row 1911 as the chart data range indicates, instead it extends to row 20467. So the chart data range will not be dynamic. I don't know why this isn't working for you, because when I tried it with a Table, it worked.

Unfortunately, the built-in Histogram does not have a SERIES formula, so you can't put the range directly into the SERIES formula.

 

[BaGRoS]

Changing HiddenTab[Weight] to HiddenTemp!$D$2:$D$1911 is normal behavior, which I described before.

Your Table's data range in your screenshot does not stop at row 1911 as the chart data range indicates, instead it extends to row 20467. So the chart data range will not be dynamic. I don't know why this isn't working for you, because when I tried it with a Table, it worked.

Unfortunately, the built-in Histogram does not have a SERIES formula, so you can't put the range directly into the SERIES formula.

I don't know also why not working for me, maybe some settings?

Perhaps some similar chart?