Macro to create charts - Points to wrong sheet

[JDB]

I have a macro in personal.xls that automatically creates charts. There are
three sheets, the first is where the user selects a name from a drop down box
and the chart is displayed. The second sheet is, I think where the problem
lies, it looks at the name the user has selected on sheet one and populates
with the data drawn from sheet three. Sheet one then displays the graph. The
problem I'm having is that the second sheet draws it's data from an earlier
workbook (results - march) and not the one I wish to use (results - october).
When I created the October sheet I copied the sheets across - the macros
where in the workbook so I moved them to my personal.xls to make them work.

The formula on the first column of the second sheet looks like this if it
helps!

=VLOOKUP(F2,'I:\CCR\SAM\IPP\FCC Results\March 2008\[Results -
March.xls]Individual Results Data'!B1:N202,1,FALSE)

I need March to read Octover but Excel says that one or more references are
invalid!

Grateful for any replies!

 

[JDB]

If it helps, here is the thisworkbook.makemycharts macro;

Sub MakeMyCharts()
'
Dim ws As Worksheet
Dim rng As Range
Dim Yaddr As String
Dim Y2addr As String
Dim Xaddr As String
Dim iRow As Long
Dim iSrs As Long
Dim iAddr As Long
Dim cht As Chart

Set ws = ActiveSheet
If TypeName(Selection) <> "Range" Then
MsgBox "Select the data range for the charts"
Exit Sub
End If
Set rng = Selection

For iRow = 2 To rng.Rows.Count

Set cht = Charts.Add
cht.Name = rng.Cells(iRow, 1).Value & " - " & rng.Cells(iRow, 2).Value
For iSrs = cht.SeriesCollection.Count To 1 Step -1
cht.SeriesCollection(iSrs).Delete
Next

With cht.SeriesCollection.NewSeries
.Name = rng.Rows(iRow).Resize(1, 2)
Yaddr = "=("
Y2addr = "=("
Xaddr = "=("
For iAddr = 3 To 9 Step 2
Yaddr = Yaddr & "'" & ws.Name & "'!" & rng.Cells(iRow,
iAddr).Address(ReferenceStyle:=xlR1C1) & ","
Y2addr = Y2addr & "'" & ws.Name & "'!" & rng.Cells(iRow, iAddr +
1).Address(ReferenceStyle:=xlR1C1) & ","
Xaddr = Xaddr & "'" & ws.Name & "'!" & rng.Cells(1,
iAddr).Address(ReferenceStyle:=xlR1C1) & ","
Next
Yaddr = Left$(Yaddr, Len(Yaddr) - 1) & ")"
Y2addr = Left$(Y2addr, Len(Y2addr) - 1) & ")"
Xaddr = Left$(Xaddr, Len(Xaddr) - 1) & ")"
.Values = Yaddr
.XValues = Xaddr
.ChartType = xlLineMarkers
End With

With cht.SeriesCollection.NewSeries
.Name = "Target"
.Values = Y2addr
.ChartType = xlColumnClustered
End With

Next
End Sub

 

[Nigel]

You need to ensure you have your references set up and that the path, file
and worksheet names exist

This part needs to be changed.....

\March 2008\[Results - March.xls]Individual Results Data'!

In you path "I:\CCR\SAM\IPP\FCC Results" is there a folder called "October
2008", in which there is the file "Results - March.xls" in which is a sheet
named "Individual Result Data" ?

 

[JDB]

Actually, forget it! I've discovered it usually helps to ensure your sheets
are named correctly! I had neglected to rename the data sheet from Sheet 1 to
Individual Results Data.

Problem now sorted!!