Visual Basic Run Time Error "1004"

I have a excell spreadsheet with a micro that worked fine on 2003 and when I upgraded to 2007 it get the error message.
"TheChart.SeriesCollection.Add Source:=Worksheets("Wood & HY Shaft Data").Range(Cells(ActiveCell.Row, "Z"), Cells(ActiveCell.Row, "AG")), SeriesLabels:=True" This is the line Flagged as a problem. Not sure what to change.

Thanks
Don Johnson

Don welcome to Excel Forum!, at present i don't have 2007 but try the modified line below

Please Login or Register  to view this content.

Excel.Worksheet worksheet = workbook.Sheets[1] as Excel.Worksheet

the above is a quote i came across for setting a worksheet as the sheet you want to use.

Hope some of this helps.

Regards,
Simon

Simon: Thanks for reply and suggestion on a fix. Typing as you have shown gives me a compile error Method or data member not found (Error 461). I have tried different combination of the quote line and still get errors of some sort. Will continue play with it.

Thanks
Don Johnson

Simon: I have given Microsoft a copy of my spreadsheet as they could not solve my problem via live help. So will post their answer when and if I get one. Thanks for your help
Don Johnson

Finished my second session with Microsoft Help Line and all that was accomplished was they agree that the Macro I have works fine on version 2003 and not on version 2007. I am getting the feeling that its my problem. Maybe someone could help me with a work around. Here is the macro:

Public Sub Update_CPMIn_W()
'update CPM_In Chart based on selections
Dim TheChart As Chart
Dim ChtSeries As SeriesCollection


Set TheChart = Charts("EI Deflection Curves")
Set ChtSeries = TheChart.SeriesCollection

'check that at least one selection has been made
'set valid range based on shaft names
RCnt = Range("G6", Range("G6").End(xlDown)).Rows.Count
Range("E6", Range("E6").Cells(RCnt, 1)).Select
Selection.Name = "CPMIn_Range"
ActiveSheet.Calculate
If RCnt - Range("E2").Value <= 0 Then
MsgBox "No Selection been made."
ActiveCell.Select
Exit Sub
End If
ActiveCell.Select


'clear all current series in chart
For i = 1 To ChtSeries.Count
TheChart.SeriesCollection(1).Delete
Next i


'add selected series to chart
For i = 1 To RCnt
Range("E6").Cells(i, 1).Activate
If ActiveCell.Value <> "" Then
'add series to chart
Charts("EI Deflection Curves").SeriesCollection.Add Source:=Worksheets("Wood & HY Shaft Data").Range(Cells(ActiveCell.Row, "Z"), Cells(ActiveCell.Row, "AG")), SeriesLabels:=True
End If
Next i
Charts("EI Deflection Curves").Select

End Sub

Don, in my first posting to you i gave a line that incorporated "Excel.Worksheet" this should read Excel.Worksheets i think, anyway have you tried recording your actions in 2007, i have heard that things don't record the same and that the ribbon is difficult to get your head around at first.

Regards,
Simon

Simon: Thanks for your help I tried recording what I was doing and got a feel for what I should be looking for. I have programed a work around that mabe a little crude but it works. So I'm off and running

Thanks
Don Johnson