Solved Move a row to another sheet if Closed or Completed

September 1, 2016 at 04:32:12
Specs: Windows 8
@DerbyDad03 provided the following code, which I amended the targets to suit my personal needs:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
If Target = "Closed" Then
Application.EnableEvents = False
If Target = "Moved" Then
Application.EnableEvents = False
nxtRow = Sheets("Closed").Range("A" & Rows.Count).End(xlUp).Row + 1
Target.EntireRow.Copy _
Destination:=Sheets("Closed").Range("A" & nxtRow)
End If
End If
Application.EnableEvents = True
End Sub

The code works for my spreadsheet when I select 'Closed' in Sheet 1, moving it to Sheet 2 perfectly. Any chance anyome could show me an amended code to prompt more than one word (target) to move a row to another sheet? Using the above example, when 'Closed' or 'Moved' is selected from a drop down list, the row moves to another sheet. Appreciate it.

See More: Move a row to another sheet if Closed or Completed

Reply ↓  Report •

September 1, 2016 at 05:40:00
✔ Best Answer
First, a posting tip:

Please click on the blue line at the end of this post and read the instructions on how to post VBA code in this forum so that it is easier for us to read. Please keep the pre tags in mind the next time you post VBA code. Thanks!

Second, it appears that you amended the code in such a manner that it no longer works.

You have 3 If instructions but only 2 End If's. That won't work.

Third, you say that you want to amend the code to "move a row to another sheet" when Moved is selected, but you don't say what sheet you want to move the row to. If it's being moved to the Closed sheet, then all you need to do is change one line:

If Target = "Closed" Or Target = "Moved" Then

If you want it moved to a different sheet, then there are numerous ways to accomplish that, depending on where you want it moved to. Please clarify that requirement and we'll take it from there.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Reply ↓  Report •

September 1, 2016 at 08:11:18
Thanks - I have managed to fix and sort! All the best

Reply ↓  Report •

Related Solutions

Ask Question