How to select, copy and paste rows from a find operation

[Lambertatg]

I have the following code that finds the string in the following VBA code. The problems is I have to re-enter the search string every time the macro runs. How can I adjust this code to select, copy and paste (as it does for the first string it finds) all the remaining rows relevant to the this search string?

Dim lngNextRow As Long
Dim strMyString As String
Sheets("ww1186").Activate

strMyString = InputBox("Enter the number you wish to find")

On Error GoTo err_Trap


Cells.Find(What:=strMyString, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext).Activate

ActiveCell.EntireRow.Copy

Sheets("Sheet1").Select
lngNextRow = Range("A" & rows.Count).End(xlUp).Row + 1
Range("A" & lngNextRow).PasteSpecial


err_Trap:
If Err.Number = 91 Then
MsgBox "Could not find " & strMyString & " anywhere on this sheet.", , "Unsuccessful search"


Can someone please help?
Many thanks
Lambert

[NeedForExcel]

You will probably have to use "For Next" for this one.

Can you attach a Sample Workbook?


Thank You,

Deep

[HaHoBe]

Hi, Lambert,

could you please wrap the code with Code-Tags?

Please try if this is what you are looking for

Please Login or Register  to view this content.

Ciao,
Holger

[Lambertatg]

Hi Holger,

Wow!!! this code really works very well indeed. Thanks a million. I have learnt a valuable lesson in how to loop with the boolean type of variable. I would not dreamed of using a boolean this way. Yes, guilty as charged...the comments about "Code Tags". I'll get this sorted right away.

thanks again

Lambert