CODEWITHSUNDEEP
excelvbaautoruncopy-data
Juan Jacobs
Juan Jacobs

Reputation: 23

How do I run a vba code every time a cell in a specific row changes?

I currently have a vba code that copies data from one worksheet to a number of new worksheets depending on the option chosen from a drop down list in column D.

I need this code to run everytime the drop down list is used on ever row.

This is the vba code that is currently working

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Sub FilterAndCopy()

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual


Dim lngLastRow As Long
Dim ActiveSheet As Worksheet, InactiveSheet As Worksheet, PendingSheet As Worksheet,     RenewedSheet As Worksheet, FollowUpSheet As Worksheet, RedZoneSheet As Worksheet

Set ActiveSheet = Sheets("Active")
Set InactiveSheet = Sheets("Inactive")
Set PendingSheet = Sheets("Pending")
Set RenewedSheet = Sheets("Renewed")
Set FollowUpSheet = Sheets("Follow Up")
Set RedZoneSheet = Sheets("Red Zone")

lngLastRow = Cells(Rows.Count, "A").End(xlUp).Row


With Range("A1", "R" & lngLastRow)
    .AutoFilter
    .AutoFilter Field:=4, Criteria1:="Active"
    .Copy ActiveSheet.Range("A1")
    .AutoFilter Field:=4, Criteria1:="Inactive"
    .Copy InactiveSheet.Range("A1")
    .AutoFilter Field:=4, Criteria1:="Pending"
    .Copy PendingSheet.Range("A1")
    .AutoFilter Field:=4, Criteria1:="Renewed"
    .Copy RenewedSheet.Range("A1")
    .AutoFilter Field:=4, Criteria1:="Follow Up"
    .Copy FollowUpSheet.Range("A1")
    .AutoFilter Field:=4, Criteria1:="Red Zone"
    .Copy RedZoneSheet.Range("A1")
    .AutoFilter
End With


Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic

End Sub

Everytime I change any cell in column D the data doesnt copy automatically. I need to manually go to developer, open visual basic and the run the module before the data will copy to the new worksheet.

Upvotes: 0

Views: 38

Answers (1)

Chronocidal
Chronocidal

Reputation: 8081

To check when the value of a cell has changed, you will trigger the Worksheet.Change event, not the Worksheet.SelectionChange event.

Then, you want to use that to check if the cell that has changed is in Column D — and, if it is, then you can run whatever code you wanted to.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim colD As Range, rngChange As Range
    
    Set colD = Intersect(Target, Me.Columns(4)) 'Get the list of Values in Column D that have changed
    
    If Not (colD Is Nothing) Then
        'A Value in Column D has been changed!  We need to do something…
        
        '''SAMPLE CODE START'''
        For Each rngChange In colD.Cells
            If rngChange.Value = "Active" Then
                MakeRowActive rngChange.EntireRow
            ElseIf rngChange.Value = "Inactive" Then
                MakeRowInActive rngChange.EntireRow
            End If
        Next rngChange
        '''SAMPLE CODE END'''
    End If
End Sub

Replace the SAMPLE CODE with whatever you need to happen when a value in Column D has been changed.

Upvotes: 0

Related Questions