Results 1 to 4 of 4

Move row to different tab based on derived value

Threaded View

  1. #1
    Registered User
    Join Date
    05-26-2010
    Location
    Indiana
    MS-Off Ver
    Excel 2003
    Posts
    3

    Move row to different tab based on derived value

    So after researching it looks like I may have the wrong workbook event, but am unsure how to change the code to accomodate a different event.

    Right now I have a 'Status' field in column H that is derived by comparing today's date to dates in columns E & F. The macro below is setup to move 'Completed' items to the 'Completed' sheet, and move all other items ('Planned', 'Unplanned','In Progress') to the 'Current State' sheet. Unfortunately, in order to trigger the macro I have to double click on the cell in column H and hit enter.

    I'm actually wanting the macro to kickoff whenever the value in column H changes (even if it is the result of a change to column E or F. I'm thinking maybe I should have the WorksheetCalculate event, but as mentioned above.

    I'd also like to autosort the data as the current code moves the active row to the bottom anytime the macro is triggered.

    In advance, I very much appreciate the time.

    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
        '   Code goes in the Worksheet specific module
        Dim rng As Range
            '   Set Target Range
            Set rng = Target.Parent.Range("H4:H500")
                '   Only look at single cell change
                If Target.Count > 1 Then Exit Sub
                '   Only look at that range
                If Intersect(Target, rng) Is Nothing Then Exit Sub
                '   Action if Condition(s) are met
                Select Case Target.Text
                    Case "COMPLETED"
                        Target.EntireRow.Cut Sheets("Completed").Cells(Rows.Count, "A").End(xlUp).Offset(0)
                    Case "UNPLANNED"
                        Target.EntireRow.Cut Sheets("Current State").Cells(Rows.Count, "A").End(xlUp).Offset(1)
                    Case "PLANNED"
                        Target.EntireRow.Cut Sheets("Current State").Cells(Rows.Count, "A").End(xlUp).Offset(1)
                    Case "IN PROGRESS"
                        Target.EntireRow.Cut Sheets("Current State").Cells(Rows.Count, "A").End(xlUp).Offset(1)
                End Select
    End Sub
    Thanks for the info
    Last edited by thenerv25; 05-26-2010 at 09:10 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1