[SOLVED]Run-Time Error 91

I've done a lot of searching, but to no avail. I'm not real familiar with macros and the one I have is one I found that is close to what I need. I have a workbook with several worksheets. I want to search through each worksheet and have the results posted to a new worksheet. What I have works unless the word I'm searching for doesn't appear on a worksheet and I get the run time error at that point.

Here is the code:

Option Explicit

Private Function FindAll(What, Optional SearchWhat As Variant, _
     Optional LookIn, Optional LookAt, _
     Optional SearchOrder, Optional SearchDirection As XlSearchDirection = xlNext, _
     Optional MatchCase As Boolean = False, _
     Optional MatchByte, Optional SearchFormat) As Range
 Dim aRng As Range
 If IsMissing(SearchWhat) Then
     On Error Resume Next
     Set aRng = ActiveSheet.UsedRange
     On Error GoTo 0
 ElseIf TypeOf SearchWhat Is Range Then
     If SearchWhat.Cells.Count = 1 Then
         Set aRng = SearchWhat.Parent.UsedRange
     Else
         Set aRng = SearchWhat
         End If
 ElseIf TypeOf SearchWhat Is Worksheet Then
     Set aRng = SearchWhat.UsedRange
 Else
     Exit Function                       '*****
     End If
 If aRng Is Nothing Then Exit Function   '*****
 Dim FirstCell As Range, CurrCell As Range
 With aRng.Areas(aRng.Areas.Count)
 Set FirstCell = .Cells(.Cells.Count)
     'This little 'dance' ensures we get the first matching _
      cell in the range first
     End With
 Set FirstCell = aRng.Find(What:=What, After:=FirstCell, _
     LookIn:=LookIn, LookAt:=LookAt, _
     SearchDirection:=SearchDirection, MatchCase:=MatchCase, _
     MatchByte:=MatchByte, SearchFormat:=SearchFormat)
 If FirstCell Is Nothing Then Exit Function          '*****
 Set CurrCell = FirstCell
 Set FindAll = CurrCell
 Do
     Set FindAll = Application.Union(FindAll, CurrCell)
         'Setting FindAll at the top of the loop ensures _
          the result is arranged in the same sequence as _
          the  matching cells; the duplicate assignment of _
          the first matching cell to FindAll being a small _
          price to pay for the ordered result
     Set CurrCell = aRng.FindNext(CurrCell)
     Loop Until CurrCell.Address = FirstCell.Address
 End Function
Sub testIt()
 MsgBox FindAll(1, , xlValues, xlWhole).Address
 MsgBox FindAll(1, , xlValues, xlPart).Address
 MsgBox FindAll("(", , xlFormulas, xlPart).Address
 MsgBox FindAll(1, Range("a1:a10"), xlValues, xlPart).Address
 On Error Resume Next
 Err.Clear
 End Sub
 
 Sub FindAndCopyOpponentInfoToTempSheet()
'Objective: Use Tushar's Findall function* to loop through all sheets in the active workbook searching for a Opponent _
& then copying the data to a new sheet.
'*sourced from http://www.tushar-mehta.com/excel/tips/findall.html

Dim ws As Worksheet
Dim TempSht As Worksheet
Dim CellOnFirstEmptyRow As Range
Dim OpponentName As String

'grab user input - it may be better to use the Input Method rather than this Input function, I'm not sure...?
OpponentName = InputBox(prompt:="Please type in the Opponent Name " & Chr(13) & "or leave the cell empty to use " _
& "the value of the active cell as the Opponent name" & Chr(13) & "or type in 'clearallfilters'", _
Title:="THE Opponent NAME IS...?")

'To end sub if "cancel" was pressed sourced from _
http://www.excelforum.com/showthread...vbcancel+input & http://vb.mvps.org/tips/varptr.asp
If StrPtr(OpponentName) = 0 Then MsgBox "Search cancelled": Exit Sub

'insert a temp sheet for pasting the results into
Worksheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = OpponentName & "Series"
Set TempSht = ActiveSheet
    
    For Each ws In ActiveWorkbook.Worksheets
        'this assumes that there will always be a value in column A, if this is not true let me know...
        Set CellOnFirstEmptyRow = TempSht.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
        FindAll(OpponentName, ws, xlValues, xlWhole).EntireRow.Copy CellOnFirstEmptyRow
SkipSht:
    Next ws

'edit: to free memory
Set TempSht = Nothing
Set CellOnFirstEmptyRow = Nothing

End Sub

The error occurs at:

 FindAll(OpponentName, ws, xlValues, xlWhole).EntireRow.Copy CellOnFirstEmptyRow

You need to test the function return:

Dim rng as Range
Set rng = FindAll(OpponentName, ws, xlValues, xlWhole)
If not rng is nothing then rng.EntireRow.Copy CellOnFirstEmptyRow

It should not duplicate anything. What actual code see you using now?

Here's the code now. What it does is creates a new worksheet and lists the results twice. If that makes sense. Example, I get the results listed lines 1-10 and then repeated lines 11-20.

Option Explicit

Private Function FindAll(What, Optional SearchWhat As Variant, _
     Optional LookIn, Optional LookAt, _
     Optional SearchOrder, Optional SearchDirection As XlSearchDirection = xlNext, _
     Optional MatchCase As Boolean = False, _
     Optional MatchByte, Optional SearchFormat) As Range
 Dim aRng As Range
 If IsMissing(SearchWhat) Then
     If Err.Number <> 0 Then Err.Clear
     Resume Next
     Set aRng = ActiveSheet.UsedRange
     If Err.Number <> 0 Then Err.Clear
     Resume Next
 ElseIf TypeOf SearchWhat Is Range Then
     If SearchWhat.Cells.Count = 1 Then
         Set aRng = SearchWhat.Parent.UsedRange
     Else
         Set aRng = SearchWhat
         End If
 ElseIf TypeOf SearchWhat Is Worksheet Then
     Set aRng = SearchWhat.UsedRange
 Else
     Exit Function                       '*****
     End If
 If aRng Is Nothing Then Exit Function   '*****
 Dim FirstCell As Range, CurrCell As Range
 With aRng.Areas(aRng.Areas.Count)
 Set FirstCell = .Cells(.Cells.Count)
     'This little 'dance' ensures we get the first matching _
      cell in the range first
     End With
 Set FirstCell = aRng.Find(What:=What, After:=FirstCell, _
     LookIn:=LookIn, LookAt:=LookAt, _
     SearchDirection:=SearchDirection, MatchCase:=MatchCase, _
     MatchByte:=MatchByte, SearchFormat:=SearchFormat)
 If FirstCell Is Nothing Then Exit Function          '*****
 Set CurrCell = FirstCell
 Set FindAll = CurrCell
 Do
     Set FindAll = Application.Union(FindAll, CurrCell)
         'Setting FindAll at the top of the loop ensures _
          the result is arranged in the same sequence as _
          the  matching cells; the duplicate assignment of _
          the first matching cell to FindAll being a small _
          price to pay for the ordered result
     Set CurrCell = aRng.FindNext(CurrCell)
     Loop Until CurrCell.Address = FirstCell.Address
 End Function
Sub testIt()
 MsgBox FindAll(1, , xlValues, xlWhole).Address
 MsgBox FindAll(1, , xlValues, xlPart).Address
 MsgBox FindAll("(", , xlFormulas, xlPart).Address
 MsgBox FindAll(1, Range("a1:a10"), xlValues, xlPart).Address
 End Sub
 
 Sub FindAndCopyOpponentInfoToTempSheet()
'Objective: Use Tushar's Findall function* to loop through all sheets in the active workbook searching for a Opponent _
& then copying the data to a new sheet.
'*sourced from http://www.tushar-mehta.com/excel/tips/findall.html

Dim Ws As Worksheet
Dim TempSht As Worksheet
Dim CellOnFirstEmptyRow As Range
Dim OpponentName As String

'grab user input - it may be better to use the Input Method rather than this Input function, I'm not sure...?
OpponentName = InputBox(prompt:="Please type in the Opponent Name " & Chr(13) & "or leave the cell empty to use " _
& "the value of the active cell as the Opponent name" & Chr(13) & "or type in 'clearallfilters'", _
Title:="THE Opponent NAME IS...?")

'To end sub if "cancel" was pressed sourced from _
http://www.excelforum.com/showthread...vbcancel+input & http://vb.mvps.org/tips/varptr.asp
If StrPtr(OpponentName) = 0 Then MsgBox "Search cancelled": Exit Sub

'insert a temp sheet for pasting the results into
Worksheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = OpponentName & " Series"
Set TempSht = ActiveSheet
    
    For Each Ws In ActiveWorkbook.Worksheets
        'this assumes that there will always be a value in column A, if this is not true let me know...
        Set CellOnFirstEmptyRow = TempSht.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
        'prevents the whole summary being duplicated
        'the below copies the whole row for each instance where the Opponent name is found. If you want to _
        limit the columns copied let me know...
Dim rng As Range
Set rng = FindAll(OpponentName, Ws, xlValues, xlWhole)
If Not rng Is Nothing Then rng.EntireRow.Copy CellOnFirstEmptyRow

Next Ws

'edit: to free memory
Set TempSht = Nothing
Set CellOnFirstEmptyRow = Nothing

End Sub

Your code loops through every sheet which includes your output sheet, which I think is why you are getting the same data twice.