Solved: Create chart on new sheet using VBA

[hunsnowboard]

Hi there Everyone!

I have a very very annoying problem. I have a sheet with data and I would like to add some charts to it, but the charts should be on another sheet.

Below you can see my code, which generates the run time error: 1004 application-defined or object-defined error for the line:
ActiveChart.SetSourceData Source:=Worksheets("DiagData").Range(Cells(2 + v * (riskDriversNo + 3), 4), Cells(2 + v * (riskDriversNo + 3), lastDataDiag).End(xlDown))
I did a search on the net I found out that the problem might be that the chart is not on the same page with the data.
But then how on earth can be this issue solved?

Here is my code:


Public Sub createChart()


Dim myDataRange As Range
Dim lastDataDiag As Long

v = 0

Worksheets("DiagData").Activate


lastDataDiag = Worksheets("DiagData").Cells(2 + v * (riskDriversNo + 3), 4).End(xlToRight).Column

Set myDataRange = Worksheets("DiagData").Range(Cells(2 + v * (riskDriversNo + 3), 4), Cells(2 + v * (riskDriversNo + 3), lastDataDiag).End(xlDown))


Worksheets("Diagram").Activate

ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlLine
ActiveChart.SetSourceData Source:=Worksheets("DiagData").Range(Cells(2 + v * (riskDriversNo + 3), 4), Cells(2 + v * (riskDriversNo + 3), lastDataDiag).End(xlDown))
End Sub

I also tried:
Source:= myDataRange ..but same error.

Please could you help me with this issue.

Thank you in advance for your help!

[p45cal]

try qualifying everything (red dots below):With Worksheets("DiagData")
Set myDataRange = .Range(.Cells(2 + v * (riskDriversNo + 3), 4), .Cells(2 + v * (riskDriversNo + 3), lastDataDiag).End(xlDown))
End With

[hunsnowboard]

Hi p45cal!

Thank you very much for your reply! It solved my problem! Thank you so much! Thanks a lot! : pray2: