Hi All,

I have a problem with FindNext. In brief, I want to search through a
column until I find some text with the word "Total" in. I then want to
copy some data to a new workbook, save that book and then carry on and
search for the next instance of the word "total" and repeat. I can get
the first part to work but then something happens when I want to find
the next instance of "total". Here is part of the code.

' Search through column B for total
With ActiveSheet.Range("B:B")
' Look in part as part of total row
Set FindBA = .Find(What:="Total", LookIn:=xlValues,
LookAt:=xlPart)
If Not FindBA Is Nothing Then
'Call sub routine that copies & pastes to new sheet
'Find next item
Do
Set FindBA = .FindNext(FindBA)
'Call sub routine that copies & pastes to new sheet
Loop While Not FindBA.Value = "Grand Total"
End If
End With

Stepping through the routine, I can see that when I call the first sub
routine, my object FINDBA changes it's value immediately after the
paste command in the sub routine. This means that when it comes back
to my main routine it is no looking for "Total". When I tested this
routine using a message box, it worked OK. It seems to be the Paste
command that causes the problem.

There are around 6000 rows of data in this column and about 70+
instances of the word Total so I do not want to loop through every
single cell which would be my alternative to using FindNext.

Any help would be much appreciated.

Regards

Glyn Baker