Moving entire row based on cell value VBA

HHutton

New Member
Joined
Nov 30, 2018
Messages
8
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
 
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.:)
 
Upvote 0

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Here is the revised macro with explanatory comments. I hope this helps.
Code:
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
 
Upvote 0
@HHutton
Please take the time to read the rules, especially Rule#13 regarding cross posting.
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top