Move row to another worksheet If Yes

April 26, 2011 at 01:33:15
Specs: Windows XP
Can anyone help me write a Marco code that will move a row in excel to another worksheet if "Yes" is entered into colomn "I"?
I have two sheets "Current" and "Completed". I want a row to automatically move from the current list to the completed worksheet when I select "Yes" from a drop down list in colomn "I". The rows remaining are to move up so there are no empty rows.
Both sheets are identical. Row 1 has my headings.
Colomns A - J are in use holding various info. Its only coloum I that I was to be the trigger.

Any response will be appreciated.

Leo


See More: Move row to another worksheet If Yes

Report •


#1
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

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


Report •

#2
April 27, 2011 at 00:35:26
DerbyDad03,

You are amazing! I've been trying to get the right code for 2 weeks. Thank you.

Cheers

Leo
(silently wishing he understood excel coding) :)

Will this affect any conditional formatting I have on the worksheet?


Report •

#3
April 27, 2011 at 05:16:17
re: Will this affect any conditional formatting I have on the worksheet?

How would I know? I have no clue what CF you have set up.

Test it.

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


Report •

Related Solutions

#4
August 10, 2011 at 05:09:32
@DerbyDad03

I have been searching for something like this for days! So thanks for getting me on the right track.

However, Is it possible to adjust your code so that it is a manually executed macro (attached to a click button for example)?
I am building a spreadsheet with other macros that crash when I use yours, I assume because it is automatic.

I've tried augmenting your code so that it is a regular macro (as opposed to a sheet's code) But i'm very amateur!

Any advise/feedback would be greatly appreciated.

Cheers,
adtc


Report •


Ask Question