I can't update chart range in Excel

Question

I'm trying to create a macro for every time a data is inserted in the last line of Excel the chart automatically updates the selected range to include all the data present in the sheet but when the program executes an error occurs, can someone help me?

Code:

Public Sub Chart_Update()

Dim k As Long

k = Cells(Rows.Count, "B").End(xlUp).Row

Range("B2:B" & k & ",E2:E" & k & ",I2:I" & k & ",K2:K" & k).Select
ActiveChart.SetSourceData Source:=Range("AC_Offset_Registers!B2:B" & k & ",E2:E" & k & ",I2:I" & k & ", K2:K" & k)

ActiveWorkbook.Close SaveChanges:=True

End Sub

Error:

score 0 · Answer 1 · answered Jun 09 '21 at 07:52

0

Change Range("AC_Offset_Registers!B2:B" … into ThisWorkbook.Worksheets("AC_Offset_Registers").Range("B2:B" … and remove the entire line before (you don't need to .Select).

You might benefit from reading How to avoid using Select in Excel VBA.

Also make sure you specify a worksheet for Cells in the same way ThisWorkbook.Worksheets("AC_Offset_Registers").Cells

Public Sub Chart_Update()
    Dim ws As Worksheet    
    Set ws = ThisWorkbook.Worksheets("AC_Offset_Registers")

    Dim k As Long
    k = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
    
    ActiveChart.SetSourceData Source:=ws.Range("B2:B" & k & ",E2:E" & k & ",I2:I" & k & ", K2:K" & k)
    
    ThisWorkbook.Close SaveChanges:=True
End Sub

answered Jun 09 '21 at 07:52

Pᴇʜ

56,719
10
49
73

Hello friend, thanks for the reply, but the error remains even after changing the code as you said – KhallP Jun 09 '21 at 07:59
@KhallP because you did not do what I did. Read carefully again. You did something else. – Pᴇʜ Jun 09 '21 at 08:00
friend I checked and it's the same :(, check the post – KhallP Jun 09 '21 at 08:03
It is not. Make sure you copy and paste my code. – Pᴇʜ Jun 09 '21 at 08:04
Then make sure to replace `ActiveChart` with a static reference to your chart. – Pᴇʜ Jun 09 '21 at 08:11
I copied your code and pasted it and it still doesn't work... – KhallP Jun 09 '21 at 12:24
I told you you need to make sure to replace `ActiveChart` with a static reference to your chart. – Pᴇʜ Jun 09 '21 at 12:34
how can i know the name of my chart? – KhallP Jun 09 '21 at 12:56
@KhallP Actually you named it so you should know. When you select the chart, its name is in the left of the formula bar. – Pᴇʜ Jun 09 '21 at 13:00
Can you check the new response I made to my post? – KhallP Jun 09 '21 at 13:10

score 0 · Answer 2 · answered Jun 09 '21 at 13:10

0

I found that the name of my chart is "Gráfico 2" but when I run the program I get a "Subscript out of range" error, could you tell me if the code below is right?

Public Sub Chart_Update()

Dim ws As Worksheet, ws_2 As Worksheet, k As Long

Set ws = Sheets("AC_Offset_Registers")
Set ws_2 = Sheets("Gráfico")
k = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row

Charts("Gráfico 2").SetSourceData Source:=ws_2.Range("B2:B" & k & ",E2:E" & k & ",I2:I" & k & ",K2:K" & k)

ThisWorkbook.Close SaveChanges:=True

End Sub

answered Jun 09 '21 at 13:10

KhallP

23
6

Please only use your original question to add code. Answers that do not provide a solution will be deleted by the community. • Try `Worksheets("Gráfico").ChartObjects("Gráfico 2").Chart.SetSourceData Source:=ws.Range("B2:B" & k & ",E2:E" & k & ",I2:I" & k & ",K2:K" & k)` – Pᴇʜ Jun 09 '21 at 13:19

I can't update chart range in Excel

2 Answers2