FindNext problem when using PasteSpecial
 

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

FindNext problem when using PasteSpecial
 

The only thing I see that would change FindBA would be if your copy routine
changes FindBA or you delete the copied rows on the source sheet in your
copy routine.

If you are deleting the copied rows, then you could have findnext as
follows:

set FindBA = .findNext(.cells(1,1))

If you are changing it in the copy routine, then don't.

anyway, this worked fine for me


Sub Tester5()
Dim findBa As Range
Dim fAddr As String
' 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
fAddr = findBa.Address
Do
copyData findBa
Set findBa = .FindNext(findBa)
Loop While Not findBa.Address = fAddr And _
findBa.Value < "Grand Total"
End If
End With
End Sub

Sub copyData(rng As Range)
rng.EntireRow.Copy Destination:=Worksheets("sheet1"). _
Cells(Rows.Count, 1).End(xlUp)(2)
End Sub

--
Regards,
Tom Ogilvy



Glyn Baker wrote in message
om...
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