Macro to copy next row

[Richyog]

Hi,
I just want to copy the next two row after the specific word.

Suppose there is a word "dynamic data" and after this word i want to copy the next two rows of that sheet to the sheet2.

Summarizing:-
1.To find a specific word by MACRO
2.Copy next two rows after this word.
3.PAste it to the new sheet

 

[VoG]

Try like this

Sub test3()
Dim Found As Range
With Sheets("Sheet1")
    Set Found = .Cells.Find(what:="dynamic data", kookin:=xlValue, lookat:=xlWhole)
    If Not Found Is Nothing Then Found.Offset(1).Resize(2).EntireRow.Copy Destination:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
End With
End Sub

 

[Richyog]

Hi Peter,

Thanks for the quick reply but i wanted that next two or three or may be 4 rows after a word can be copied.
This macro is giving an error i.e."Name argument not found"
What is this error.Why it is showing there.What mistake I had done so that i am getting this error

 

[VoG]

Sorry, I had a couple of typos. Try

Sub test3()
Dim Found As Range, n As Long
n = Application.InputBox("How many rows", Type:=1)
With Sheets("Sheet1")
    Set Found = .Cells.Find(what:="dynamic data", LookIn:=xlValues, lookat:=xlWhole)
    If Not Found Is Nothing Then Found.Offset(1).Resize(n).EntireRow.Copy Destination:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
End With
End Sub

 

[Richyog]

Hi,

Its working,mind blowing thanx really......