I am writing a macro that will search through multiple sheets in a workbook, looking for every occurence of a word. For each "hit" I copy the row containing the hit into a different workbook. The macro is structured as follows: There is one loop for each sheet. For each sheet I continue searching and copying 'til I find the first hit again. at that point I exit the loop. That loop is inside a bigger loop that cycles through all the sheets in the workbook. If there ar no hits in a worksheet, Excel creates an error. I have a "On Error GoTo Done" statement where Done: is outside the inner loop (i.e. when an error occurs I am done with that sheet and move on to the next sheet). I also reset Err = 0. This appears to work fine. When there are multiple sheets with no hits, the program crashes. It is able to catch the first error occurence, but not the second (Error 91 btw).

Any suggestions?

Can't really tell you how to alter your code unless we see it.

--

Regards,
Tom Ogilvy

"OkieViking" wrote in message
...
I am writing a macro that will search through multiple sheets in a
workbook, looking for every occurence of a word. For each "hit" I copy the
row containing the hit into a different workbook. The macro is structured
as follows: There is one loop for each sheet. For each sheet I continue
searching and copying 'til I find the first hit again. at that point I exit
the loop. That loop is inside a bigger loop that cycles through all the
sheets in the workbook. If there ar no hits in a worksheet, Excel creates
an error. I have a "On Error GoTo Done" statement where Done: is outside
the inner loop (i.e. when an error occurs I am done with that sheet and move
on to the next sheet). I also reset Err = 0. This appears to work fine.
When there are multiple sheets with no hits, the program crashes. It is
able to catch the first error occurence, but not the second (Error 91 btw).

Any suggestions?

Didn't see any error handling, but have adjusted the code so it doesn't
raise an error if nothing is found.

Sub Macro1()
'
'
Dim NewBook, OldBook As String
'
On Error GoTo Done
Windows("Magnolia Lessons Learned Edited.xls").Activate

'

OldBook = ActiveWorkbook.Name
Workbooks.Add
NewBook = ActiveWorkbook.Name
Windows(OldBook).Activate
For Each d In OldBook.Worksheets
d.Activate
MsgBox (ActiveSheet.Name)
Cells.Select
Selection.Name = "SearchRange"
set rng = Selection.Find(What:="casing", _
After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
if not rng is nothing then
rng.Select
firstaddress = Selection.Address
lastaddress = ActiveCell.Address
Selection.EntireRow.Select
Selection.Copy
Windows(NewBook).Activate
ActiveSheet.Paste
Selection.Offset(1, 0).Select
Windows(OldBook).Activate


For i = 1 To 10000
Range("searchrange").Select
Range(lastaddress).Activate
set rng = Selection.FindNext(After:=ActiveCell)
if not rng is nothing then
rng .activate
lastaddress = ActiveCell.Address
If lastaddress = firstaddress Then GoTo Done
ActiveCell.Select
Selection.EntireRow.Select
Selection.Copy
Windows(NewBook).Activate
ActiveSheet.Paste
Selection.Offset(1, 0).Select

Windows(OldBook).Activate
End if
Next i
End if
Done:
MsgBox ("tr" & Err)
Range("searchrange").Select
workbooks(OldBook).Names("SearchRange").Delete
Err = 0
Next
End Sub

--
Regards,
Tom Ogilvy

"OkieViking" wrote in message
...
Sub Macro1()
'
'
Dim NewBook, OldBook As String
'
On Error GoTo Done
Windows("Magnolia Lessons Learned Edited.xls").Activate

'

OldBook = ActiveWorkbook.Name
Workbooks.Add
NewBook = ActiveWorkbook.Name
Windows(OldBook).Activate
For Each d In Sheets
Windows(OldBook).Activate

d.Activate
MsgBox (ActiveSheet.Name)
Cells.Select
Selection.Name = "SearchRange"
Selection.Find(What:="casing", After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False).Activate

ActiveCell.Select
firstaddress = Selection.Address
lastaddress = ActiveCell.Address
Selection.EntireRow.Select
Selection.Copy
Windows(NewBook).Activate
ActiveSheet.Paste
Selection.Offset(1, 0).Select
Windows(OldBook).Activate


For i = 1 To 10000
Range("searchrange").Select
Range(lastaddress).Activate
Selection.FindNext(After:=ActiveCell).Activate
lastaddress = ActiveCell.Address
If lastaddress = firstaddress Then GoTo Done
ActiveCell.Select
Selection.EntireRow.Select
Selection.Copy
Windows(NewBook).Activate
ActiveSheet.Paste
Selection.Offset(1, 0).Select

Windows(OldBook).Activate
Next i

Done:
MsgBox ("tr" & Err)
Range("searchrange").Select
Selection.Name.Delete
Err = 0
Next
End Sub

"Tom Ogilvy" wrote:

Can't really tell you how to alter your code unless we see it.

--

Regards,
Tom Ogilvy

"OkieViking" wrote in message
...
I am writing a macro that will search through multiple sheets in a
workbook, looking for every occurence of a word. For each "hit" I copy
the
row containing the hit into a different workbook. The macro is
structured
as follows: There is one loop for each sheet. For each sheet I continue
searching and copying 'til I find the first hit again. at that point I
exit
the loop. That loop is inside a bigger loop that cycles through all the
sheets in the workbook. If there ar no hits in a worksheet, Excel
creates
an error. I have a "On Error GoTo Done" statement where Done: is
outside
the inner loop (i.e. when an error occurs I am done with that sheet and
move
on to the next sheet). I also reset Err = 0. This appears to work
fine.
When there are multiple sheets with no hits, the program crashes. It is
able to catch the first error occurence, but not the second (Error 91
btw).

Any suggestions?