CODEWITHSUNDEEP
excelvbacharts
Vivin Gavas
Vivin Gavas

Reputation: 1

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

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!

Upvotes: 0

Views: 363

Answers (1)

A.S.H
A.S.H

Reputation: 29352

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

Upvotes: 1

Related Questions