Repeat a macro

[Diane K]

I have a client using Excel 2000. They have a data sheet with over 9000
lines of information. She basically wants to find, say from column D, every
cell that contains the word "bearing" and copy that cell to another
worksheet. She has a macro that does the copying, but what she wants to do
and I don't know how, is to make it repeat until the job is finished.

Any ideas?

Thanks!
Dino

 

[Guest]

First way involves no macro at all. Click on the top of column D and:

Data > Filter > AutoFilter > Custom... > Contains > bearing

This will hide the cells in Column D without" bearing". Just copy the
visible cells and paste eslewhere.

 

[Ron de Bruin]

See this page Diane
http://www.rondebruin.nl/copy5.htm

 

[Guest]

Second way is with a macro:

Sub diane()
Set r1 = Intersect(ActiveSheet.UsedRange, Range("D"))
Set r2 = Sheets("Sheet2").Range("D1")
i = 0
For Each r In r1
If InStr(r.Value, "bearing") > 0 Then
r1.Copy r2.Offset(i, 0)
i = i + 1
End If
Next
End Sub

This will search & copy till the job is done.

 

[Diane K]

Thanks everyone! The easiest route by far was using the AutoFilter. Duh....I
should have thought of it!!!