|
In visual basic
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 |
In visual basic
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 |
In visual basic
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 |
In visual basic
|
In visual basic
many thanks this has done the job......
"Don Guillett" wrote: 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 |
| All times are GMT +1. The time now is 05:17 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com