Hi
I've written a macro which updates a clustered column chart in Sheet 1 that's based on data in a table in Sheet 2. However, the chart is not fully updating.
The ‘source data’ for the chart is =Table5[#All]
(based on a table that currently exists in Sheet2).
The table in Sheet 2 is based on data that is in Sheet 3 (where data has been filtered and duplicates removed).
However, when I run the macro below (see code below) to update the chart:
Does anyone know how to ensure the chart scale updates automatically, given that it’s based on a table created by the macro?
Please find below sample data and the macro:
In Sheet2, there is a table with the headers "Chocolate Type" and "Count," in cells A1 and B1, respectively.
There are also three chocolate types (Mars, Snickers and Boost) in cells A2, A3 and A4, respectively.
And the numbers 2, 3, and 4 in cells B2, B3, and B4, respectively.
[TABLE="width: 182"]
<tbody>[TR]
[TD]Chocolate Type
[/TD]
[TD]Count
[/TD]
[/TR]
[TR]
[TD]Mars
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]Snickers
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]Boost
[/TD]
[TD]4
[/TD]
[/TR]
</tbody>[/TABLE]
In Sheet1, there is a chart in cell A1 that is based on the data from Sheet2.
In Sheet3, there is the following data - plain text - not in a table (please note that there is an extra row here for Lindt chocolate!)
[TABLE="width: 177"]
<tbody>[TR]
[TD]Chocolate Type
[/TD]
[TD]Count
[/TD]
[/TR]
[TR]
[TD]Mars
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]Snickers
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]Mars
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]Lindt
[/TD]
[TD]5
[/TD]
[/TR]
</tbody>[/TABLE]
In Sheet4, there is the data below which you can manually copy and paste into Sheet3 to check if the chart actually updates with additional records – NB – I deleted 3 rows in Sheet3 to see if the chart would become less wide, but it didn’t
[TABLE="width: 191"]
<tbody>[TR]
[TD]Chocolate Type
[/TD]
[TD]Count
[/TD]
[/TR]
[TR]
[TD]Mars
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]Snickers
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]Boost
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]Lindt
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]Maltesers
[/TD]
[TD]6
[/TD]
[/TR]
[TR]
[TD]Ferrero Rocher
[/TD]
[TD]4
[/TD]
[/TR]
</tbody>[/TABLE]
And this is the Sub - enlightenment would be greatly appreciated! Have a good weekend!
I've written a macro which updates a clustered column chart in Sheet 1 that's based on data in a table in Sheet 2. However, the chart is not fully updating.
The ‘source data’ for the chart is =Table5[#All]
(based on a table that currently exists in Sheet2).
The table in Sheet 2 is based on data that is in Sheet 3 (where data has been filtered and duplicates removed).
However, when I run the macro below (see code below) to update the chart:
- It doesn’t show additional records
- When records have been deleted in Sheet3, the width of the chart is still the same – it shows less records eg if there were only 2 records in Sheet3 after deleting 3 (from a total of 5), it would show the two remaining records but the chart would NOT become smaller!
Does anyone know how to ensure the chart scale updates automatically, given that it’s based on a table created by the macro?
Please find below sample data and the macro:
In Sheet2, there is a table with the headers "Chocolate Type" and "Count," in cells A1 and B1, respectively.
There are also three chocolate types (Mars, Snickers and Boost) in cells A2, A3 and A4, respectively.
And the numbers 2, 3, and 4 in cells B2, B3, and B4, respectively.
[TABLE="width: 182"]
<tbody>[TR]
[TD]Chocolate Type
[/TD]
[TD]Count
[/TD]
[/TR]
[TR]
[TD]Mars
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]Snickers
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]Boost
[/TD]
[TD]4
[/TD]
[/TR]
</tbody>[/TABLE]
In Sheet1, there is a chart in cell A1 that is based on the data from Sheet2.
In Sheet3, there is the following data - plain text - not in a table (please note that there is an extra row here for Lindt chocolate!)
[TABLE="width: 177"]
<tbody>[TR]
[TD]Chocolate Type
[/TD]
[TD]Count
[/TD]
[/TR]
[TR]
[TD]Mars
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]Snickers
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]Mars
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]Lindt
[/TD]
[TD]5
[/TD]
[/TR]
</tbody>[/TABLE]
In Sheet4, there is the data below which you can manually copy and paste into Sheet3 to check if the chart actually updates with additional records – NB – I deleted 3 rows in Sheet3 to see if the chart would become less wide, but it didn’t
[TABLE="width: 191"]
<tbody>[TR]
[TD]Chocolate Type
[/TD]
[TD]Count
[/TD]
[/TR]
[TR]
[TD]Mars
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]Snickers
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]Boost
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]Lindt
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]Maltesers
[/TD]
[TD]6
[/TD]
[/TR]
[TR]
[TD]Ferrero Rocher
[/TD]
[TD]4
[/TD]
[/TR]
</tbody>[/TABLE]
And this is the Sub - enlightenment would be greatly appreciated! Have a good weekend!
Code:
Sub TestChart()
Sheet2.Activate
Range("A1").CurrentRegion.Select
Selection.ClearContents
Sheet3.Activate
Range("a1", Range("b1").End(xlDown)).Select
Selection.Copy
Sheet2.Activate
Range("a1").PasteSpecial xlPasteValues
'create a table from the data just pasted
Dim tbl As ListObject
Set tbl = ActiveSheet.ListObjects.Add(xlSrcRange, Selection, , xlYes)
tbl.TableStyle = "TableStyleMedium15"
End Sub