Hi
You are right, yes, i need to copy subsequent rows and maximum 30 rows.
My expected is When i run your code and open box for row number of column
G[38,567,1299,4567,...] then related rows copy to sheet2. criteria is values
in ColG.

Regards

"Jacob Skaria" wrote:

--The macro copies all rows containing the search value in ColG.

--If you mean to copy subsequent rows to the other sheet; then you havent
mentioned how many rows to be copied or the criteria to know how many
subsequent rows are to be copied.

Post back with sample data.

--
Jacob (MVP - Excel)


"climate" wrote:

Hello Jacob
Thank you, but my second problem is exist. i want to select several rows not
one row.
regards

"Jacob Skaria" wrote:

Try the below. Edit the sheet names to suit...

Sub CopyRow()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim varFound As Variant, varSearch As Variant
Dim strAddress As String, lngLastRow As Long

Set ws1 = Sheets("Sheet1") 'source sheet
Set ws2 = Sheets("Sheet2") 'destination sheet

varSearch = InputBox("Find which number in row G and copy it?")
If varSearch = "" Then Exit Sub

With ws1.Columns("G")
Set varFound = .Find(varSearch, LookIn:=xlValues, LookAt:=xlWhole)
If Not varFound Is Nothing Then
strAddress = varFound.Address
Do
lngLastRow = ws2.Cells(Rows.Count, "G").End(xlUp).Row + 1
ws1.Rows(varFound.Row).Copy ws2.Rows(lngLastRow)
Set varFound = .FindNext(varFound)
Loop While Not varFound Is Nothing And _
varFound.Address < strAddress
End If
End With
End Sub

--
Jacob (MVP - Excel)


"climate" wrote:

Hi
I have following code for row selecting based on number of column G and copy
to sheet (winter), but i have 2 problem. 1) for example: when i input 38 to
box, row with number of 13856 be selected. 2) i need to select multiple row
for copy to sheet1 not one row.
Sub CopyRow()
Dim Answer As String
Dim LastRowOnwinter As Long
With Worksheets("sheet1")
LastRowOnwinter = .Cells(.Rows.Count, "A").End(xlUp).Row
If LastRowOnwinter = 1 And .Cells(1, "A").Value = "" Then
LastRowOnwinter = 0
End If
Answer = InputBox("Find which number in row G and copy it?")
Worksheets("winter").Columns("G").Find(Answer).Ent ireRow. _
Copy .Range("A" & (LastRowOnwinter + 1))
End With
End Sub

Would you please guide me?
regards