Updating a chart selection: error 1004 vba

Question

I look for different reason why I

Basically I'm updating a chart based on a pivot table (note that in my case for some reason I cannont use the Pivot with Chart, it simply doesn't work). So I have my database in sheet PROCESS when deactivating the sheet my macro updates the pivot table (that I have no problem with).

But then from the pivot a chart is created. The issue is when I want to update the chart. I obviously have to increase the row of data, otherwise the chart stays the same.

So here's my macro for updating the chart.

Sub update_chart()

Dim Sht As Worksheet
'Select the Time elapse to start from somewhere
Set Sht = Sheets("Pivot")

Sheets("Calendar").Select
    ActiveChart.SeriesCollection(1).Select
  'Update Initial request
    ActiveChart.SeriesCollection(1).Values = Sht.Range("D4", Range("D4").End(xlDown))
  'Update Vertical Axis
    ActiveChart.SeriesCollection(1).XValues = Sht.Range("C4:A4", Range("C4:A4").End(xlDown))
  'Update Planning Period
    ActiveChart.SeriesCollection(2).Values = Sht.Range("F4", Range("F4").End(xlDown))
  'Update Posting Date
    ActiveChart.SeriesCollection(3).Values = Sht.Range("G4", Range("G4").End(xlDown))
  'Update Assessment Period
    ActiveChart.SeriesCollection(4).Values = Sht.Range("H4", Range("H4").End(xlDown))
  'Update Availability
    ActiveChart.SeriesCollection(5).Values = Sht.Range("I4", Range("I4").End(xlDown))

End Sub

I get a Run-time Error 1004 Method Range of object _Worksheet failed.

Basically I want the macro to update the chart with the new data that is in the Pivot Sheet and to ensure to NOT forget any rows. I though by adding the Range X, range.Xendxldown would work but apparently it does not.

I would appreciate any comment that could help me. Thank you very much

SJR SJR · Accepted Answer · 2017-03-09T17:08:12

Try this. You need to add sheet references to every range reference. Also safer to work up from the top rather than down just in case you have only one entry.

Sub update_chart()

Dim Sht As Worksheet
'Select the Time elapse to start from somewhere
Set Sht = Sheets("Pivot")
Sheets("Calendar").Select
    ActiveChart.SeriesCollection(1).Select
  'Update Initial request
    ActiveChart.SeriesCollection(1).Values = Sht.Range("D4", Sht.Range("D" & Sht.Rows.Count).End(xlUp))
  'Update Vertical Axis
    ActiveChart.SeriesCollection(1).XValues = Sht.Range("A4", Sht.Range("A" & Sht.Rows.Count).End(xlUp)).Resize(, 2)
  'Update Planning Period
    ActiveChart.SeriesCollection(2).Values = Sht.Range("F4", Sht.Range("F" & Sht.Rows.Count).End(xlUp))
  'Update Posting Date
    ActiveChart.SeriesCollection(3).Values = Sht.Range("G4", Sht.Range("G" & Sht.Rows.Count).End(xlUp))
  'Update Assessment Period
    ActiveChart.SeriesCollection(4).Values = Sht.Range("H4", Sht.Range("H" & Sht.Rows.Count).End(xlUp))
  'Update Availability
    ActiveChart.SeriesCollection(5).Values = Sht.Range("I4", Sht.Range("I" & Sht.Rows.Count).End(xlUp))

End Sub

Updating a chart selection: error 1004 vba

1 Answers