-1

I have 2 worksheets (lets name them worksheet1 and worksheet2) in 1 workbook (single excel file). workseet1 contains 2 columns : Description and Column (2 choices : Completed and In-Progress).

Description and Status(Completed/In-Progress)

My requirement: When I select the status = In-progress (from the drop-down list), this entire row must be moved to the second worksheet i.e worksheet2. So that I am left with only the rows which have the status = Completed in the worksheet. That is to say, worksheet1 will contain only rows which have Status = Completed and second worksheet i.e. worksheet2 will contain only rows which contain Status = In-progress.

The moment I select any option from the Status, it gets moved to its respective worksheet.

TIA

2
  • What have you tried ? This is not a post your demand and have your work done for you site...
    – Solar Mike
    Feb 4, 2018 at 7:13
  • Why? Why would you want to move data about when the status changes? What is the bigger picture? You can summarize data based on status in a report or in a chart or a dashboard without moving it. Moving data to different sheets is bad data architecture. Data should live on one sheet, then reporting can be done in other sheets.
    – teylyn
    Feb 4, 2018 at 8:13

1 Answer 1

1

Not entirely sure why you want to break up your data, but you can use the Worksheet_Change() event in VBA to accomplish what you're after.

This goes in the Completed Worksheet's Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    'This goes into your "Completed" worksheet's module

    Dim RngB As Range
    Set RngB = Intersect(Target, Range("B:B"))

    If RngB Is Nothing Then Exit Sub
    Application.EnableEvents = False

    Dim cel As Range, wsInProgress As Worksheet, retVal As Variant

    '!!! Change the worksheet name to whatever it is that moves from your completed
    'worksheet to the in-progress worksheet...
    Dim wsInProgress As Worksheet
    Set wsInProgress = ThisWorkbook.Worksheets("In-Progress")

    For Each cel In RngB.Cells
        Debug.Print cel.Address
        If cel.Value = "In-Progress" Then
            wsInProgress.Rows(nextrow(wsInProgress)) = cel.EntireRow.Value
            cel.EntireRow.Delete
        End If
    Next
    Application.EnableEvents = True
End Sub

This goes in the In-Progress Worksheet's Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    'This goes into your "In-Progress" worksheet's module

    Dim RngB As Range
    Set RngB = Intersect(Target, Range("B:B"))

    If RngB Is Nothing Then Exit Sub
    Application.EnableEvents = False

    Dim cel As Range, wsInProgress As Worksheet, retVal As Variant

    '!!! Change the worksheet name to whatever it is that moves from your completed
    'worksheet to the in-progress worksheet...
    Dim wsCompleted As Worksheet
    Set wsCompleted = ThisWorkbook.Worksheets("Completed")

    For Each cel In RngB.Cells
        Debug.Print cel.Address
        If cel.Value = "Completed" Then
            wsInProgress.Rows(nextrow(wsCompleted)) = cel.EntireRow.Value
            cel.EntireRow.Delete
        End If
    Next
    Application.EnableEvents = True
End Sub

This goes into a Standard Module:

Public Function nextRow(ByVal ws As Worksheet, Optional ByVal col As Variant = 1) As Long
    nextRow = ws.Cells(ws.Rows.Count, col).End(xlUp).Row + 1
End Function

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .