VBA error : Getting Run-time error '91': Object variable or With block variable not set When trying to change chart data

Question

I'm trying to copy two worksheets of an existing Workbook to a new workbook. One of the sheets has charts. These charts still reference the old workbook and I want to change data source to new file(The second sheet I copy is the data sheet). I wrote the following code and I am getting the error mentioned above

NewBook.Sheets(1).Activate
    ActiveWorkbook.ActiveSheet.ChartObjects("Chart 2").Activate
     ActiveChart.SetSourceData Source:=NewBook.Sheets(2).Range("B14:E20")

I Think the error could be that when I select the chart and change data, the previous workbook is activated and hence there is no longer an active sheet. So I tried this code

NewBook.Sheets(1).Charts("Chart 2").SetSourceData Source:=NewBook.Sheets(2).Range("B5:F11")

I'm now getting the Run time error 438. Could someone help me with this and help me understand what's happening. Thanks a lot in advance!

A.S.H · Accepted Answer · 2017-07-20 19:10:36Z

1

No need to use any of the Activate stuff. You attempt to get rid of it was correct. Only a little modification is needed:

NewBook.Sheets(1).ChartObjects("Chart 2").Chart.SetSourceData _
  NewBook.Sheets(2).Range("B5:F11")

answered Jul 20, 2017 at 19:10

A.S.H

29.4k6 gold badges25 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA error : Getting Run-time error '91': Object variable or With block variable not set When trying to change chart data

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related