Move Row from one worksheet to another based on cell value

danjameswalker

New Member
Joined
Oct 23, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Firstly; I'm aware this has been asked loads but I just can't figure it out!

I would like to move an row from my Worksheet "Action Log" to another worksheet "Closed Actions" when an "x" is input in the Complete column of my table.

From what I understand I need to use VBA code but I've tried putting a few of the code strings in with no luck.
Each row has some formulas in and some conditional formatting. The Complete column only has conditional formatting in. I don't know if that confuses it

I have never used VBA before so could someone break it down as if youre talking to an infant :)

1603465390947.png
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,238
Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your "Action Log" sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Put an "x" in column K and press the RETURN key.
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("K:K")) Is Nothing Then Exit Sub
    Target.EntireRow.Copy Sheets("Closed Actions").Cells(Sheets("Closed Actions").Rows.Count, "A").End(xlUp).Offset(1)
End Sub
 

danjameswalker

New Member
Joined
Oct 23, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Thank you, how do I now get it to delete the row from Action Log but leave it in ClosedActions
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,238
Try:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("K:K")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    Target.EntireRow.Copy Sheets("Closed Actions").Cells(Sheets("Closed Actions").Rows.Count, "A").End(xlUp).Offset(1)
    Target.EntireRow.Delete
    Application.EnableEvents = True
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,127,589
Messages
5,625,671
Members
416,125
Latest member
NeedExcelHelp2021

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
Top