In visual basic

[Chuck]

How do i, after pressing a button find a row in sheet 1 which has "Max" in
colum F

Ser Ser No Date Insp Rate Date Next Insp Test Type
Remarks
A001 AG1123 15-04-07 S 15-04-08 Max
A002 AG1265 15-04-07 S 15-04-08 Min
A003 AG1556 17-05-07 S 17-05-08 Anu
A004 AG2314 15-03-07 S 15-03-08 Max
A005 AH435R1 17-07-07 U/S 17-07-08 Anu


and then in sheet 2

Paste the entire row with "Max" in and then find the next blank row and
paste the next entire row with "Max" in from sheet 1 into sheet 2.
there could be 100 rows that have "Max" in and they all need to be copied
and pasted from sheet1 to sheet2.

i have had some help in the past but all i get is errors.

Can any one help?

Many thanks

 

[Don Guillett]

Look in the vba help index for FINDNEXT. There is a good example.

 

[Mike H]

Chuck,

Press Alt+F11 to open VB editor, insert a new module and paste this in and
run it:-
it's not case sensitive and will find both Max, and max.
You can assign a button to run it from the worksheet if you want.

Sub copyit()
Dim MyRange, MyRange1 As Range
Sheets("Sheet1").Select
LastRow = Sheets("Sheet1").Range("F65536").End(xlUp).Row
Set MyRange = Sheets("Sheet1").Range("F1:F" & LastRow)
For Each C In MyRange
If UCase(C.Value) = "MAX" Then
If MyRange1 Is Nothing Then
Set MyRange1 = C.EntireRow
Else
Set MyRange1 = Union(MyRange1, C.EntireRow)
End If
End If
Next
MyRange1.Copy

Sheets("Sheet2").Select
Range("A1").Select
ActiveSheet.Paste
End Sub

Mike

 

[Chuck]

Many thanks Mike

But i keep geting a 400 Error, i can see it finds it all but it wont paste.
can you help.

 

[Don Guillett]

If that is the only problem, try this instead
MyRange1.Copy Sheets("Sheet2").Range("A1")

 

[Chuck]

many thanks this has done the job......

If that is the only problem, try this instead
MyRange1.Copy Sheets("Sheet2").Range("A1")

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)