runtime error '1004': method 'range' of object '_global' failed

[sn281]

Hi there, thanks for looking.

I have a simple bit of code that I would like to loop through each column and then graph this.
I have my dates in column A and then B, C, D..... etc are full of numbers. I would like them individually graphed into new sheets.

I have written this code but for some reason it fails (see comment on the line that fails).
I have declared the worksheet and when I hover over it knows that ".Name" is equal to "Economic_P&L" (the sheet name) and then the .Address has the correct "$B$2:$B$227", but it just won't run.

Can anyone help? And why is it that it does this? So I can learn

Thanks,
sn281

Sub Create_Graphs()


Dim ws As Worksheet
Dim ch As Chart
Dim rng As Range
Dim i As Long


Set ws = ThisWorkbook.Sheets("Economic_P&L")
Set rng = ws.Range("$B$2:$B$227")


For i = 0 To 39
With ws
    Set ch = .Shapes.AddChart.Chart.Location(xlLocationAsNewSheet, .Range("B2").Offset(0, i))
    ch.ChartType = xlLine
    ch.SetSourceData Source:=Range(.Name & "!" & rng.Offset(0, i).Address) 'fails on this line
    ch.SeriesCollection(1).XValues = "=Sheet1!$A$2:$A$227"


End With
Next


Set rng = Nothing
Set ws = Nothing


End Sub

 

[Sektor]

Source:=.Range(.Name & "!" & rng.Offset(0, i).Address)

 

[hiker95]

sn281,

Try adding a . immediately in front of Range

ch.SetSourceData Source:=.Range(.Name & "!" & rng.Offset(0, i).Address)

 

[sn281]

Adding a "." now gives an error of: [h=2]runtime error '1004': method 'range' of object '_Worksheet' failed[/h]

 

[RoryA]

Try this:

ch.SetSourceData Source:=rng.Offset(0, i)

 

[sn281]

Thanks so much all for your replies. That last post from RoryA has solved the issue. I don't fully understand why, but thanks a lot!
sn281