Option Explicit

Sub Macro1()
Dim ws As Worksheet
Dim found As Range
Dim addr As String
Dim col As Collection
Set col = New Collection
For Each ws In Worksheets
Set found = ws.Cells.Find("daily")
If Not found Is Nothing Then
addr = found.Address
Do
col.Add ws.Name & found.Address(False, False)
Set found = ws.Cells.FindNext(found)
Loop Until found.Address = addr

End If


Next

Dim index As Long
If col.Count 0 Then
Set ws = Worksheets.Add
ws.Activate
For index = 1 To col.Count
ws.Cells(index, 1) = col(index)
Next
Else
MsgBox "no cells found"
End If


End Sub


"mp" wrote:

Hi all,
Is there an easy way to search all used cells in a workbook to detect
references to named ranges?
eg if I name a range "Labor2009" and a cell somewhere has a value like =
"Labor2009" how could i search that
something like pseudocode
For each oCell in oWorkBook.UsedCells(if such a collection existed)
If oCell.Value Like ("*target name*") then
'found reference
End if
Next
thanks
mark