Move Copied row to next Blank Row in another Worksheet

Gerry Hunt

New Member
Joined
Apr 10, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Try this:

VBA Code:
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
 
Upvote 0
Try this:

VBA Code:
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
 
Upvote 0
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")
 
Upvote 0
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
 
Upvote 0
@Gerry Hunt, try replacing the line:

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

with:

VBA Code:
    i = .Cells.Find("*", , xlFormulas, , xlByRows, xlPrevious).Row + 1
 
Upvote 0
Change this:
VBA Code:
    i = .Range("H" & Rows.Count).End(3).Row + 1

For this:
VBA Code:
    i = .UsedRange.Rows(.UsedRange.Rows.Count).Row + 1
 
Upvote 0
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
 
Upvote 0
It should be like this:

VBA Code:
LR = Cells(Rows.Count, 1).End(xlUp).Row + 1
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,266
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top