copy multiple rows

[climate]

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).EntireRow. _
Copy .Range("A" & (LastRowOnwinter + 1))
End With
End Sub

Would you please guide me?
regards

 

[Jacob Skaria]

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

 

[climate]

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

 

[Jacob Skaria]

--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.

 

[climate]

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]

OK>So do you mean the subsequent rows are blank or will it have the same
numbers...If they do have the same numbers then the earlier macro should copy
those too....Its much easier for someone to pick up when you explain with
sample data...

--
Jacob (MVP - Excel)

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

--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.

 

[climate]

No, any cell of column G has special value and not repeated.
I need to a macro similar to earlier but with capability of several rows
selecting.
If my desciption is not sufficient, please tell me how can i send sample data.

regards

OK>So do you mean the subsequent rows are blank or will it have the same
numbers...If they do have the same numbers then the earlier macro should copy
those too....Its much easier for someone to pick up when you explain with
sample data...

--
Jacob (MVP - Excel)

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

--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)


:

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

:

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)


:

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).EntireRow. _
Copy .Range("A" & (LastRowOnwinter + 1))
End With
End Sub

Would you please guide me?
regards

 

[Jacob Skaria]

Post sample data of Col G...
--
Jacob (MVP - Excel)

No, any cell of column G has special value and not repeated.
I need to a macro similar to earlier but with capability of several rows
selecting.
If my desciption is not sufficient, please tell me how can i send sample data.

regards

OK>So do you mean the subsequent rows are blank or will it have the same
numbers...If they do have the same numbers then the earlier macro should copy
those too....Its much easier for someone to pick up when you explain with
sample data...

--
Jacob (MVP - Excel)

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

:

--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)


:

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

:

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)


:

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).EntireRow. _
Copy .Range("A" & (LastRowOnwinter + 1))
End With
End Sub

Would you please guide me?
regards

 

[climate]

Hi
Column G consist of 4000 cell (1 to 4000 value) which their values
distributed irregular, for example(G2= 25 ........ G4000=17).
Please send me your e-mail if above my describtion not sufficient , to send
my colG.

regards

Post sample data of Col G...
--
Jacob (MVP - Excel)

No, any cell of column G has special value and not repeated.
I need to a macro similar to earlier but with capability of several rows
selecting.
If my desciption is not sufficient, please tell me how can i send sample data.

regards

OK>So do you mean the subsequent rows are blank or will it have the same
numbers...If they do have the same numbers then the earlier macro should copy
those too....Its much easier for someone to pick up when you explain with
sample data...

--
Jacob (MVP - Excel)


:

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

:

--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)


:

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

:

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)


:

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).EntireRow. _
Copy .Range("A" & (LastRowOnwinter + 1))
End With
End Sub

Would you please guide me?
regards

 

[climate]

Hello Jacob
How can i Post sample data of Col G...?
regards

Post sample data of Col G...
--
Jacob (MVP - Excel)

No, any cell of column G has special value and not repeated.
I need to a macro similar to earlier but with capability of several rows
selecting.
If my desciption is not sufficient, please tell me how can i send sample data.

regards

OK>So do you mean the subsequent rows are blank or will it have the same
numbers...If they do have the same numbers then the earlier macro should copy
those too....Its much easier for someone to pick up when you explain with
sample data...

--
Jacob (MVP - Excel)


:

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

:

--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)


:

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

:

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)


:

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).EntireRow. _
Copy .Range("A" & (LastRowOnwinter + 1))
End With
End Sub

Would you please guide me?
regards