Solved Move row to another worksheet If Yes...

April 6, 2017 at 13:33:55
Specs: Windows 10
and then to a different worksheet if No from same dropdown.

I am currently using this code by DerbyDad03


DerbyDad03 April 26, 2011 at 13:12:49

Right Click the sheet tab for the sheet where you will be entering Yes.
Choose View Code.
Paste this code into the pane that opens:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 9 Then
If Target = "Yes" Then
Application.EnableEvents = False
nxtRow = Sheets("Completed").Range("I" & Rows.Count).End(xlUp).Row + 1
Target.EntireRow.Copy _
Destination:=Sheets("Completed").Range("A" & nxtRow)
Target.EntireRow.Delete
End If
End If
Application.EnableEvents = True
End Sub

Can someone tweak this so if I select no from dropdown in column 9 it will be moved to a worksheet named "Denied"

Thanks in advanced!



See More: Move row to another worksheet If Yes...

Report •

#1
April 6, 2017 at 14:17:25
Please click on the following line and read the instructions on how to post VBA code in this forum. Then repost the code using the pre tags so that it retains it original formatting. Thanks!

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


Report •

#2
April 6, 2017 at 14:50:13
Sorry Derby, Hopefully this is better.

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

message edited by Ross4552


Report •

#3
April 6, 2017 at 15:38:51
✔ Best Answer
Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Column = 9 Then
  Application.EnableEvents = False
   If Target = "Yes" Then
      nxtRow = Sheets("Completed").Range("I" & Rows.Count).End(xlUp).Row + 1
       Target.EntireRow.Copy _
        Destination:=Sheets("Completed").Range("A" & nxtRow)
       Target.EntireRow.Delete
  ElseIf Target = "No" Then
      nxtRow = Sheets("Denied").Range("I" & Rows.Count).End(xlUp).Row + 1
       Target.EntireRow.Copy _
        Destination:=Sheets("Denied").Range("A" & nxtRow)
       Target.EntireRow.Delete
  End If
 End If
 Application.EnableEvents = True
End Sub

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


Report •

Related Solutions

#4
April 7, 2017 at 06:45:39
Thanks so much DerbyDad03

Report •

#5
April 7, 2017 at 06:51:09
Now that you have some working code, you should consider reviewing the debugging techniques found in the following tutorial and applying them to the code. Once you understand how the code works, you should be able to make some these tweaks yourself.

Of course, you can always ask your questions here in the forum, but you might save yourself some time by doing what you can on your own.

https://www.computing.net/howtos/sh...

message edited by DerbyDad03


Report •

Ask Question