Thread
:
In visual basic
View Single Post
#
5
Posted to microsoft.public.excel.worksheet.functions
Don Guillett
external usenet poster
Posts: 10,124
In visual basic
If that is the only problem, try this instead
MyRange1.Copy Sheets("Sheet2").Range("A1")
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"Chuck" wrote in message
...
Many thanks Mike
But i keep geting a 400 Error, i can see it finds it all but it wont
paste.
can you help.
"Mike H" wrote:
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" wrote:
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
Reply With Quote
Don Guillett
View Public Profile
Find all posts by Don Guillett