Chart based on table not updating as expected (VBA)

[Mr2017]

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:

1. It doesn’t show additional records
2. 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.

Chocolate Type	Count
Mars	2
Snickers	3
Boost	4

<tbody>
</tbody>

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!)

Chocolate Type	Count
Mars	2
Snickers	3
Mars	4
Lindt	5

<tbody>
</tbody>

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

Chocolate Type	Count
Mars	2
Snickers	3
Boost	4
Lindt	5
Maltesers	6
Ferrero Rocher	4

<tbody>
</tbody>

And this is the Sub - enlightenment would be greatly appreciated! Have a good weekend!

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

 

[RoryA]

I'd suggest you stop clearing the existing table and then adding a new one, and simply overwrite the data rows of the existing table.

 

[Mr2017]

Hi Rory

Thanks for the prompt response

How would I do that using VBA?

If the new records I'm importing into the table are less than the existing ones, then surely the table would be inaccurate? Because it would accept the new data up to the row where the old data starts, but still keep the old data?

To clarify, my point, if Sheet2 has the table below (with Mars, Snickers and Boost - three rows of data) and Sheet3 has a table with only two rows of data eg Lindt and Ferroro Rocher, then when you overwrite the table in Sheet2 with data from Sheet3, the "Boost" record in Sheet2 will still be there, because there was nothing to over-write it?

Chocolate Type	Count
Mars	2
Snickers	3
Boost	4

<tbody>
</tbody>

That's why I had been clearing the table to ensure that all the old records were deleted.

Can you please clarify how I could implement your suggestion, instead?

Thanks in advance.

 

[RoryA]

Like this (I added comments so hopefully it's clear what is happening):

Sub TestChart()

   Dim lo As ListObject
   Set lo = Sheet2.ListObjects(1)
   ' clear data part of table and resize to 1 row
   lo.DataBodyRange.Clear
   lo.Resize lo.HeaderRowRange.Resize(2)

   ' copy data excluding headers
   With Sheet3.Range("A1").CurrentRegion
      .Resize(.Rows.Count - 1).Offset(1).Copy
   End With

   ' paste to first row of table (it will autosize by default)
   lo.Range(2, 1).PasteSpecial xlPasteValues

End Sub

 

[Mr2017]

Great!!

Thank you for posting this!

 

[RoryA]

Glad to help.