Moving entire row based on cell value VBA

[HHutton]

Hello,

I need assistance with VBA to move a row to another sheet.

I have 300+ rows of data in a sheet called "MASTER". A status marker (A or X) is placed in column T. When an X is placed in column T, I would like the entire row (including blanks to save structure) to be moved to the sheet called, "Inactive". I have a code inserted, but it's not working. It is moving the entire set of data to the other sheet, not just a single row. It's also leaving the original data and not deleting it after pasting to the other sheet.

It's in conjunction with another (working) code that is very important for the file, and maybe that's the issue. I have the first working code in the part that's worksheet specific and the one that's malfunctioning is in a module.

I am extremely inexperienced in VBA and I'm not sure how to fix this.

Any help is appreciated!

Code is pasted below:

Thanks!!!

Option ExplicitSub Hutton()
Dim s1 As Worksheet, s2 As Worksheet
Set s1 = Sheets("MASTER")
Set s2 = Sheets("Inactive")
Dim lr As Long, lr2 As Long
Dim i As Long
lr = s1.Range("T" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
For i = 2 To lr
lr2 = s2.Range("A" & Rows.Count).End(xlUp).Row
If s1.Range("T" & i) = "A" Or s1.Range("T" & i) = "X" Then
s1.Range("T" & i).EntireRow.Copy s2.Range("A" & lr2 + 1)
End If
Next i
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

 

[HHutton]

So it works much better than what I was using. I have a couple small requests if you can help me edit what's there.
1. Is it possible to rewrite it for Column A on the hyperlinks? I had a request to move the Equipment code in column C.
2. Is it possible to add a line for deleting the row once it's moved to "Inactive"?
3. I know it's possible to add comments that don't affect the code that basically let you know what the code is doing. I would absolutely love to know how the macro works. Could you add a few comments to explain it? At your convenience of course as it's hardly necessary.

 

[mumps]

Here is the revised macro with explanatory comments. I hope this helps.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Intersect(Target, Range("A:A,T:T")) Is Nothing Then Exit Sub 'restricts triggering the macro to input in columns A and T
    With Application
        .ScreenUpdating = False 'turns off screen refreshing to prevent screen flicker and speed up the macro
        .EnableEvents = False 'prevents the macro from entering an infinite loop so it runs only once
    End With
    Dim bottomA As Long, rng As Range
    bottomA = Range("A" & Rows.Count).End(xlUp).Row 'last used row in column A
    Select Case Target.Column 'detects which column has been clicked for data entry
        Case Is = 1 'if target column is 1, execute the code below
            MyPath = "P:\PERSONNEL FOLDERS\OFFICE STAFF\Heather\Pics for Heather to put into tracker\"
            For Each rng In Range("A1:A" & bottomA) 'loops through values in column A
                If rng <> "" Then 'checks if cell is blank
                    MyFileName = "" 'empties MyFileName variable
                    MyFileName = Dir(MyPath & rng.Value & ".jpg", vbNormal + vbDirectory) ''defines full path
                    If MyFileName <> "" Then
                        ActiveSheet.Hyperlinks.Add Anchor:=rng, Address:=MyPath & rng.Value & ".jpg" 'adds hyperlink
                    End If
                End If
            Next rng
        Case Is = 20 'if target column is 20, execute the code below
            If Target = "X" Then
                Target.EntireRow.Copy Sheets("Inactive").Cells(Sheets("Inactive").Rows.Count, "A").End(xlUp).Offset(1, 0) 'copies row to first blank row in "Inactive" sheet
                Target.EntireRow.Delete 'deletes the row after copying/pasting
            End If
    End Select
    With Application
        .ScreenUpdating = True 'turns screen refreshing back on
        .EnableEvents = True 're-enables macros
    End With
End Sub

 

[HHutton]

You are amazing! Thank you so much!!

 

[mumps]

My pleasure.

 

[Fluff]

@HHutton
Please take the time to read the rules, especially Rule#13 regarding cross posting.