How to insert rows as value?

[nk]

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 are:


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

 

[Joel]

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]

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]

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]

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]

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