Excel Macro-Pasting on next available row [Closed]

Report
-
Posts
2744
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 10, 2021
-
Hello,
I am having issues with my macro, I hope someone can help. I have a list, where I would like the macro to see if the value in Column J "Paid", if yes paste to Sheet3 on the next available row. and to do this for the whole table. I also need a continuous list, so it needs to find the next available row each time I run the macro.

Then, I would like the Macro to go back to Sheet 2 and change all the Rows that =Paid in Column J to "N/A"

PLEASE HELP!!!

1 reply

Posts
2744
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 10, 2021
461
Hi Tomris,

I don't know if I understand you entirely, but let's start with this code. Make sure you have a back up file, so you don't loose any data when the desired result is not achieved.

Sub MoveData()
Dim lRow, lRow2 As Integer
lRow = Sheets("Sheet2").Range("J" & Rows.Count).End(xlUp).Row
For Each cell In Sheets("Sheet2").Range("J2:J" & lRow)
If cell.Value = "Paid" Then
lRow2 = Sheets("Sheet3").Range("J" & Rows.Count).End(xlUp).Row
cell.EntireRow.Copy Destination:=Sheets("Sheet3").Range("A" & lRow2 + 1)
cell.Value = "N\A"
End If
Next cell
End Sub

Best regards,
Trowa

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!