-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

  • What have you tried ? This is not a post your demand and have your work done for you site... – Solar Mike Feb 4 '18 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 '18 at 8:13
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
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.