Macro paste values in current row when text changes

[mhay5656]

Hi,
I need a VBA/Macro for disabling lookups in the current row of column D when text changes to "Today" .

For example, I have 100 rows and 5 columns in my sheet, in column 4 (D) if the cell shows "Today" I want the entire rows lookups to be disable or paste values to stop the lookups.
Thank you in advance.
Maureen

 

[Joe4]

In order to come up with automated VBA code, we first need to understand exactly how the data is being updated.
How is the text in column D being changed to the word "Today"?
Is something manually entering it?

 

[mhay5656]

Hi
It is being updated by a drop down box.

 

[Joe4]

Right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste this VBA code in the resulting VB Editor window.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
    Dim r As Long
    
    Set rng = Intersect(Target, Range("D:D"))
    If rng Is Nothing Then Exit Sub
                
    Application.EnableEvents = False
    
    For Each cell In rng
        If cell = "Today" Then
            r = cell.Row
            Rows(r).Copy
            Cells(r, "A").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
            Application.CutCopyMode = False
        End If
    Next cell
    
    Application.EnableEvents = True
    
End Sub

This code will run automatically, as column D is updated.

 

[mhay5656]

Thank you so much, works perfectly. Have been tyring to get this VBA for a long time.

 

[Joe4]

You are welcome.