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 = .Range("H" & Rows.Count).End(3).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

 

[Gerry Hunt]

Try this:

Sub MoveCopiedRow()
  Dim i As Long
  With Sheets("CASH")
    i = .Range("H" & Rows.Count).End(3).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

Very many thanks for your help with this. When running the VBA first time I get the following indication:
Run-time error '9':
Subscript out of range

Running it a second time, I get:
Can't execute code in break in mode

Debugger has highlighted the line With Sheets ("CASH") if that helps.

If you can help again - It will be most appreciated.......Gerry Hunt

 

[DanteAmor]

Switch to next worksheet entitled “CASH”

In your description you put that you have a sheet called "CASH", so create a sheet called "CASH".
Or if the sheet has another name, then update the macro and change the name of your sheet on this line:

With Sheets("CASH")

 

[Gerry Hunt]

ADVERTISEMENT

In your description you put that you have a sheet called "CASH", so create a sheet called "CASH".
Or if the sheet has another name, then update the macro and change the name of your sheet on this line:

With Sheets("CASH")

Thank you for your last input - I already had a worksheet call "CASH" but decided to create a new worksheet called "CASH" and to delete the old one. The VBA macro now works except it just transfers rows of data to the end of/underneath "previously transferred rows of data" and overwrites any rows of data in "CASH" that have been directly entered. I am looking for the macro to find the next empty/blank row at the bottom of all occupied rows to post the data not just underneath the last row transferred. i hope this makes sense. again many thanks for your help. Gerry Hunt

 

[johnnyL]

@Gerry Hunt, try replacing the line:

    i = .range("H" & Rows.Count).End(3).Row + 1

with:

    i = .Cells.Find("*", , xlFormulas, , xlByRows, xlPrevious).Row + 1

 

[DanteAmor]

ADVERTISEMENT

Change this:

    i = .Range("H" & Rows.Count).End(3).Row + 1

For this:

    i = .UsedRange.Rows(.UsedRange.Rows.Count).Row + 1

 

[Gerry Hunt]

Have tried both of the submitted suggestions for which many thanks. I afraid that for each I get a "Compile error" - "Syntax error" in each case with the following arising:

Sub Last_Row()

Dim LR As Long

LR = Cells(Rows.Count, 1).End(xlUp).Row + 1. Select


End Sub
Do you have any thoughts?
Best regards,
Gerry Hunt

 

[DanteAmor]

It should be like this:

LR = Cells(Rows.Count, 1).End(xlUp).Row + 1

 

[Gerry Hunt]

Sorry I don't see how this fits into the original VBA script that you provided. If it is not too difficult, perhaps you could be kind and send me a new script with all the corrections inserted.
many thanks,
Gerry Hunt