Move Copied row to next Blank Row in another Worksheet

[Gerry Hunt]

Can somebody please help with the following problem. I need a macro/VBA to do the following:

(Use relative references) ??

Macro record


1. Copy cells in columns A to M cells in selected row in current worksheet.

2. Switch to next worksheet entitled “CASH” (note both worksheets have identical column formats)

3. Paste the copied row values in the next blank row in the CASH worksheet (this is the area in which I am having the problem)

4. Move to the pasted row - column K cell.

5. Cut the value in the row/column K cell and paste in the row column J cell.

6. Move to the row/column I cell and insert text “ From Bank”.

7. Move to the row Column H cell – delete the existing value and replace with “1.3”

Stop macro recording

 

[DanteAmor]

Try this:

Sub MoveCopiedRow()
  Dim i As Long
  With Sheets("CASH")
    i = .UsedRange.Rows(.UsedRange.Rows.Count).Row + 1
    .Range("A" & i & ":M" & i).Value = Range("A" & ActiveCell.Row & ":M" & ActiveCell.Row).Value
    .Range("J" & i).Value = .Range("K" & i).Value
    .Range("K" & i).Value = ""
    .Range("I" & i).Value = "From Bank"
    .Range("H" & i).Value = "1.3"
  End With
End Sub