Share via

Excel formula help: If cell= yes then transfer row on to another sheet?

Anonymous
2017-12-05T14:47:14+00:00

Hi there,

I am trying to create a spread sheet to control jobs at work. when a job has been completed I would like the row to be automatically transferred to another sheet (Labelled complete).

 At the moment I have created a column called "job complete" that has a drop down option, either yes or no. when the yes option is selected I would like the row to transfer.

Is this possible? Any help would be greatly appreciated.

Daz

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Anonymous
    2017-12-06T11:13:36+00:00

    Sorry, that was my fault. On the sheet I used for testing, I was using column "V" and forgot to change the column number to suit what you had posted. 

    I had column 22 and it should have been 9 for column "I".  The other 2 changes were correct based on your sheet names.

    Complete code forllows..

    Private Sub Worksheet_Change(ByVal Target As Range)

     Application.EnableEvents = False

     On Error GoTo ErrHnd

     If Target.Column = 9 And UCase(Target.Text) = "YES" Then

     Dim rngCell As Range

     Dim rngDest As Range

     Dim strRowAddr As String

     strRowAddr = Target.Address

     Set rngDest = Worksheets("Closed"). _

     Range("A" & CStr(Application.Rows.Count)).End(xlUp).Offset(1, 0)

     Target.EntireRow.Cut Destination:=rngDest

     Application.CutCopyMode = False

     Worksheets("Master Works Register").Range(strRowAddr).EntireRow.Delete _

     Shift:=xlUp

     End If

    ErrHnd:

     Err.Clear

     Application.EnableEvents = True

     End Sub

    1 person found this answer helpful.
    0 comments

5 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2017-12-06T10:30:42+00:00

    Hi Ashish,

    Thank you very much for your reply, I will take a look.

    Daz

    0 comments
  2. Anonymous
    2017-12-06T10:29:45+00:00

    Hi Don,

    Thank you very much for helping with this.

    The names of my sheets are as follows: Sheet 1= Front page, Sheet 2= Master works Register, Sheet 3= Closed

    The picture I posted is sheet 2.

    I have copied the Code in to the VBA of the "Master works Register" sheet and changed the jobs to "Master works Register"

    unfortunately when I select YES in column I ( job complete) nothing happens.

    I also changed: Set rngDest = Worksheets("Complete"). _   to   Set rngDest = Worksheets("Closed"). _

    but still no luck.

    I really appreciate your help with this.

    Daz

    0 comments
  3. Ashish Mathur 101.8K Reputation points Volunteer Moderator
    2017-12-06T00:10:52+00:00

    Hi,

    You may refer to my solution - Split data into multiple tabs.

    Hope this helps.

    0 comments
  4. Anonymous
    2017-12-05T19:05:09+00:00

    The following code would have to go on your VBA for the sheet, not a separate module.  Also where I have the Jobs in bold, this would have to be changed to the actual name of your sheet.

    Private Sub Worksheet_Change(ByVal Target As Range)

     Application.EnableEvents = False

     On Error GoTo ErrHnd

     If Target.Column = 22 And UCase(Target.Text) = "YES" Then

     Dim rngCell As Range

     Dim rngDest As Range

     Dim strRowAddr As String

     strRowAddr = Target.Address

     Set rngDest = Worksheets("Complete"). _

     Range("A" & CStr(Application.Rows.Count)).End(xlUp).Offset(1, 0)

     Target.EntireRow.Cut Destination:=rngDest

     Application.CutCopyMode = False

     Worksheets("Jobs").Range(strRowAddr).EntireRow.Delete _

     Shift:=xlUp

     End If

    ErrHnd:

     Err.Clear

     Application.EnableEvents = True

     End Sub

    0 comments