I have a UDF (see code below). I made a copy of the sheet in which the UDF is
used, and the UDF in the original sheet now seems to be looking up values in
the copy sheet.

Can someone help amend the UDF to correct this? From what i can see i can:

1. Add a range argument (none currently exists for the range the UDF
operates on) But this would require editing a large number of formulae

2. Force the udf to always refer to the correct range no matter how many
copies of sheets are made.


CODE:
Function REFR(Product As Range, Colhead As Range, Avars As String)

Dim c As Range
Dim cs As Integer
Dim p As Integer
Dim sumthese()

REFR = "NF"

bFound = False


For Each c In Colhead.Cells

If Trim(Cells((c.Row - 1), (c.Column)).Value) = Trim(Avars) And
Trim(c.Value) = Trim(Product) Then
sumthese() = Range(Cells(c.Row + 1, c.Column), Cells(46, c.Column))
bFound = True
Exit For
End If

Next c

If bFound Then
REFR = sumthese
End If

End Function