Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy multiple rows
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy multiple rows
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy multiple rows
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy multiple rows
--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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy multiple rows
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy multiple rows
OKSo 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" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy multiple rows
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" wrote: OKSo 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" wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy multiple rows
Post sample data of Col G...
-- Jacob (MVP - Excel) "climate" wrote: 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" wrote: OKSo 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" wrote: 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy multiple rows
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" wrote: Post sample data of Col G... -- Jacob (MVP - Excel) "climate" wrote: 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" wrote: OKSo 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" wrote: 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy multiple rows
Hello Jacob
How can i Post sample data of Col G...? regards "Jacob Skaria" wrote: Post sample data of Col G... -- Jacob (MVP - Excel) "climate" wrote: 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" wrote: OKSo 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy rows to multiple sheets | Excel Discussion (Misc queries) | |||
COPY MULTIPLE ROWS INSERTED BELOW EACH | Excel Discussion (Misc queries) | |||
Copy Multiple Rows Excel | Excel Discussion (Misc queries) | |||
Copy Multiple Rows into One Cell | Excel Discussion (Misc queries) | |||
copy multiple rows from a database | Excel Programming |