| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to insert rows as value?
Hello,
I am tring to copy the entire row from one sheet and past as value to another sheet. Since I need to continue this for all the rows which meet conditions I set up, I need to move rows to past all of them. The codes I have now a Sheets("JGB").Select Range("A4").Select Do Until ActiveCell.Value = "" If ActiveCell.Offset(0, 18).Value = "Payment" Then 'If ActiveCell.Offset(0, 18).Value = "Payment" Then Application.CutCopyMode = False ActiveCell.EntireRow.Select selection.Copy Sheets("JGB Coupon Sort").Select Rows("2:2").Select selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("JGB").Select Else End If ActiveCell.Offset(1, 0).Select Loop With the codes above, I will keep pasting rows in the same row... I think I need to use something like Selection.Insert Shift:=xlDown but this does not past data as value. Could anyone please show me how to insert the entire row as value? Thank you. nk |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to insert rows as value?
Sheets("JGB").Select
Range("A4").Select Do Until ActiveCell.Value = "" If ActiveCell.Offset(0, 18).Value = "Payment" Then 'If ActiveCell.Offset(0, 18).Value = "Payment" Then Application.CutCopyMode = False ActiveCell.EntireRow.Select selection.Copy Sheets("JGB Coupon Sort").Activate LastRow = Cells(Rows.Count, "A").End(xlup).Row Rows((LastRow + 1) & ":" & (LastRow + 1)).Select selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("JGB").Select Else End If ActiveCell.Offset(1, 0).Select Loop "nk" wrote: Hello, I am tring to copy the entire row from one sheet and past as value to another sheet. Since I need to continue this for all the rows which meet conditions I set up, I need to move rows to past all of them. The codes I have now a Sheets("JGB").Select Range("A4").Select Do Until ActiveCell.Value = "" If ActiveCell.Offset(0, 18).Value = "Payment" Then 'If ActiveCell.Offset(0, 18).Value = "Payment" Then Application.CutCopyMode = False ActiveCell.EntireRow.Select selection.Copy Sheets("JGB Coupon Sort").Select Rows("2:2").Select selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("JGB").Select Else End If ActiveCell.Offset(1, 0).Select Loop With the codes above, I will keep pasting rows in the same row... I think I need to use something like Selection.Insert Shift:=xlDown but this does not past data as value. Could anyone please show me how to insert the entire row as value? Thank you. nk |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to insert rows as value?
Thank you, Joel.
However, the code seems to past only first row which has "Payment" in column S in "JGB" sheet. It does not past the rest 3 rows. Is anything else need to be added? Thank you for help again. nk "Joel" wrote: Sheets("JGB").Select Range("A4").Select Do Until ActiveCell.Value = "" If ActiveCell.Offset(0, 18).Value = "Payment" Then 'If ActiveCell.Offset(0, 18).Value = "Payment" Then Application.CutCopyMode = False ActiveCell.EntireRow.Select selection.Copy Sheets("JGB Coupon Sort").Activate LastRow = Cells(Rows.Count, "A").End(xlup).Row Rows((LastRow + 1) & ":" & (LastRow + 1)).Select selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("JGB").Select Else End If ActiveCell.Offset(1, 0).Select Loop "nk" wrote: Hello, I am tring to copy the entire row from one sheet and past as value to another sheet. Since I need to continue this for all the rows which meet conditions I set up, I need to move rows to past all of them. The codes I have now a Sheets("JGB").Select Range("A4").Select Do Until ActiveCell.Value = "" If ActiveCell.Offset(0, 18).Value = "Payment" Then 'If ActiveCell.Offset(0, 18).Value = "Payment" Then Application.CutCopyMode = False ActiveCell.EntireRow.Select selection.Copy Sheets("JGB Coupon Sort").Select Rows("2:2").Select selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("JGB").Select Else End If ActiveCell.Offset(1, 0).Select Loop With the codes above, I will keep pasting rows in the same row... I think I need to use something like Selection.Insert Shift:=xlDown but this does not past data as value. Could anyone please show me how to insert the entire row as value? Thank you. nk |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to insert rows as value?
I'm not usre what you are asking. The LastRow is checking column "A" to
determine where the last row of data is located. You can change "A" to any column. You code is copying only the row that is active. Is you want more than one row then do the following: from: ActiveCell.EntireRow.Select selection.Copy to: Range(ActiveCell, ActiveCell.Offset(2, 0)).EntireRow.Copy Note: Your original code could of been done in one row ActiveCell.EntireRow.Copy "nk" wrote: Thank you, Joel. However, the code seems to past only first row which has "Payment" in column S in "JGB" sheet. It does not past the rest 3 rows. Is anything else need to be added? Thank you for help again. nk "Joel" wrote: Sheets("JGB").Select Range("A4").Select Do Until ActiveCell.Value = "" If ActiveCell.Offset(0, 18).Value = "Payment" Then 'If ActiveCell.Offset(0, 18).Value = "Payment" Then Application.CutCopyMode = False ActiveCell.EntireRow.Select selection.Copy Sheets("JGB Coupon Sort").Activate LastRow = Cells(Rows.Count, "A").End(xlup).Row Rows((LastRow + 1) & ":" & (LastRow + 1)).Select selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("JGB").Select Else End If ActiveCell.Offset(1, 0).Select Loop "nk" wrote: Hello, I am tring to copy the entire row from one sheet and past as value to another sheet. Since I need to continue this for all the rows which meet conditions I set up, I need to move rows to past all of them. The codes I have now a Sheets("JGB").Select Range("A4").Select Do Until ActiveCell.Value = "" If ActiveCell.Offset(0, 18).Value = "Payment" Then 'If ActiveCell.Offset(0, 18).Value = "Payment" Then Application.CutCopyMode = False ActiveCell.EntireRow.Select selection.Copy Sheets("JGB Coupon Sort").Select Rows("2:2").Select selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("JGB").Select Else End If ActiveCell.Offset(1, 0).Select Loop With the codes above, I will keep pasting rows in the same row... I think I need to use something like Selection.Insert Shift:=xlDown but this does not past data as value. Could anyone please show me how to insert the entire row as value? Thank you. nk |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to insert rows as value?
Sorry... your codes were working. I found it was another problem. I raised
another question with title "How to select cells in specific Columns?". I would appreciate it if you could take a look at it, too. Thank you. "Joel" wrote: I'm not usre what you are asking. The LastRow is checking column "A" to determine where the last row of data is located. You can change "A" to any column. You code is copying only the row that is active. Is you want more than one row then do the following: from: ActiveCell.EntireRow.Select selection.Copy to: Range(ActiveCell, ActiveCell.Offset(2, 0)).EntireRow.Copy Note: Your original code could of been done in one row ActiveCell.EntireRow.Copy "nk" wrote: Thank you, Joel. However, the code seems to past only first row which has "Payment" in column S in "JGB" sheet. It does not past the rest 3 rows. Is anything else need to be added? Thank you for help again. nk "Joel" wrote: Sheets("JGB").Select Range("A4").Select Do Until ActiveCell.Value = "" If ActiveCell.Offset(0, 18).Value = "Payment" Then 'If ActiveCell.Offset(0, 18).Value = "Payment" Then Application.CutCopyMode = False ActiveCell.EntireRow.Select selection.Copy Sheets("JGB Coupon Sort").Activate LastRow = Cells(Rows.Count, "A").End(xlup).Row Rows((LastRow + 1) & ":" & (LastRow + 1)).Select selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("JGB").Select Else End If ActiveCell.Offset(1, 0).Select Loop "nk" wrote: Hello, I am tring to copy the entire row from one sheet and past as value to another sheet. Since I need to continue this for all the rows which meet conditions I set up, I need to move rows to past all of them. The codes I have now a Sheets("JGB").Select Range("A4").Select Do Until ActiveCell.Value = "" If ActiveCell.Offset(0, 18).Value = "Payment" Then 'If ActiveCell.Offset(0, 18).Value = "Payment" Then Application.CutCopyMode = False ActiveCell.EntireRow.Select selection.Copy Sheets("JGB Coupon Sort").Select Rows("2:2").Select selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("JGB").Select Else End If ActiveCell.Offset(1, 0).Select Loop With the codes above, I will keep pasting rows in the same row... I think I need to use something like Selection.Insert Shift:=xlDown but this does not past data as value. Could anyone please show me how to insert the entire row as value? Thank you. nk |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to insert rows as value?
I'm looking at your questions and your code. I try to aviod selecting and
activating cells. I prefer to use just the cell name. you could change as follows from: Range("A4").Select Do Until ActiveCell.Value = "" If ActiveCell.Offset(0, 18).Value = "Payment" Then to: Do until Range("A4").value = "" If Range("R4").Value = "Payment" Then selecting a whole column is the same as selecting a whole row LastCol = cells(1,Columns.Count).end(xltoleft).Column columns((LastCol + 1) & ":" & (Lastcol + 1)).Select selecting individual cells in a column set MyRange = Range(cells(1,"A"),(LastRow,"A")) for each cell in MyRange if not isempty(cell) then end if next cell "nk" wrote: Sorry... your codes were working. I found it was another problem. I raised another question with title "How to select cells in specific Columns?". I would appreciate it if you could take a look at it, too. Thank you. "Joel" wrote: I'm not usre what you are asking. The LastRow is checking column "A" to determine where the last row of data is located. You can change "A" to any column. You code is copying only the row that is active. Is you want more than one row then do the following: from: ActiveCell.EntireRow.Select selection.Copy to: Range(ActiveCell, ActiveCell.Offset(2, 0)).EntireRow.Copy Note: Your original code could of been done in one row ActiveCell.EntireRow.Copy "nk" wrote: Thank you, Joel. However, the code seems to past only first row which has "Payment" in column S in "JGB" sheet. It does not past the rest 3 rows. Is anything else need to be added? Thank you for help again. nk "Joel" wrote: Sheets("JGB").Select Range("A4").Select Do Until ActiveCell.Value = "" If ActiveCell.Offset(0, 18).Value = "Payment" Then 'If ActiveCell.Offset(0, 18).Value = "Payment" Then Application.CutCopyMode = False ActiveCell.EntireRow.Select selection.Copy Sheets("JGB Coupon Sort").Activate LastRow = Cells(Rows.Count, "A").End(xlup).Row Rows((LastRow + 1) & ":" & (LastRow + 1)).Select selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("JGB").Select Else End If ActiveCell.Offset(1, 0).Select Loop "nk" wrote: Hello, I am tring to copy the entire row from one sheet and past as value to another sheet. Since I need to continue this for all the rows which meet conditions I set up, I need to move rows to past all of them. The codes I have now a Sheets("JGB").Select Range("A4").Select Do Until ActiveCell.Value = "" If ActiveCell.Offset(0, 18).Value = "Payment" Then 'If ActiveCell.Offset(0, 18).Value = "Payment" Then Application.CutCopyMode = False ActiveCell.EntireRow.Select selection.Copy Sheets("JGB Coupon Sort").Select Rows("2:2").Select selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("JGB").Select Else End If ActiveCell.Offset(1, 0).Select Loop With the codes above, I will keep pasting rows in the same row... I think I need to use something like Selection.Insert Shift:=xlDown but this does not past data as value. Could anyone please show me how to insert the entire row as value? Thank you. nk |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| insert rows in a worksheet that do not change adjoining rows | Excel Discussion (Misc queries) | |||
| Insert rows: Formats & formulas extended to additonal rows | Excel Worksheet Functions | |||
| Insert page breaks every 50 rows but do not include hidden rows | Excel Programming | |||
| How do i insert of spacer rows between rows in large spreadsheets | Excel Discussion (Misc queries) | |||
| Copy Rows and insert these rows before a page break | Excel Programming | |||
Hybrid Mode
