Search for specific test and copy row+next row to new sheet

namilo786

New Member
Joined
Jun 5, 2012
Messages
1
I am really new with VBS and need help.
I have sheet1 and sheet2. All my raw data is in sheet1 column A and sheet2 will be summary report. I want my script to search each cell in column A and search for "Grade A". If find, copy entired row which contains Grade A word and also copy next row and paste it in sheet2. Here is an example.
Sheet1:
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
A-B-C
GradeA-sdfasdf-sadfsadf
Address-sadfas-sdfsadfs
Grade B-sadfsd-dgfdsgdf
Address-sdfasf-ertertewt
Grade C-fhgfdgh-ukjtyuyt
GradeA-hhh-lll
Address-ppp-hhh
<o:p></o:p>
Sheet2 shoud look like this after running script.
<o:p></o:p>
A-B-C
GradeA-sdfasdf-sadfsadf
Address-sadfas-sdfsadfs
GradeA-hhh-lll
Address-pp-hhh

<o:p></o:p>
This is what I have so far but this scrip does not loop. It only copies first found entry.

<o:p></o:p>
Sub testme()
Dim FoundCell As Range
<o:p></o:p>
With Worksheets("Sheet1")
Set FoundCell = .Cells.Find(What:="*GradeA*", _
After:=.Cells(.Cells.Count), LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
End With
<o:p></o:p>
If FoundCell Is Nothing Then
MsgBox "Not found"
Else
FoundCell.Resize(2, 1).EntireRow.Copy
Sheet2.Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
<o:p></o:p>
End If
End Sub
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
On a blank line type in range.find, then press F1. Select Range.Find Method.

Look at the example provided.
 
Upvote 0

Forum statistics

Threads
1,219,161
Messages
6,146,657
Members
450,706
Latest member
LGVBPP

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top