copy multiple rows

C

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
 
J

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
 
C

climate

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

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

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
 
J

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)


climate said:
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 said:
--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.
 
C

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

Jacob Skaria said:
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)


climate said:
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 said:
--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
 
J

Jacob Skaria

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


climate said:
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

Jacob Skaria said:
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)


climate said:
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
 
C

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

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


climate said:
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

Jacob Skaria said:
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
 
C

climate

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

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


climate said:
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

Jacob Skaria said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top