Move Entire Row to another sheet based on cell value

[Deanname]

Hello all! I am new to VBA and I am having a hard time figuring this out.

I want to move an entire row to "Sheet1" if column "H" has the letters L5P in it.
Once the row is moved to "Sheet1" I want it deleted from the "ActiveSheet"

I want this to work on multiple different files so the first sheet would not be specific but would call the "ActiveSheet"

I hope that makes sense. Thanks!

If you could put out where I could edit it, that'd be great too. For example. Let's say I need to use the exact same macro but have it say "Ford" instead of "L5P", I'd like to be able to do that.

Anddddd... go!

 

[alansidman]

Here is some aircode (untested) that may work for you

Option Explicit


Sub Dean()
    Dim s1 As Worksheet
    Dim s2 As Worksheet
    Set s1 = ActiveSheet
    Set s2 = Sheets("Sheet1")
    Dim i As Long, lr As Long
    lr = s1.Range("H" & Rows.Count).End(xlUp).Row
    Dim lrs2 As Long
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    For i = lr To 1 Step -1
        lr2S = s2.Range("A" & Rows.Count).End(xlUp).Row + 1
        If InStr(s1.Range("H" & i), "L5P") > 0 Then
            s1.Range("H" & i).EntireRow.Cut s2.Range("A" & lr2S)
            s1.Range("H" & i).EntireRow.Delete
        End If
    Next i
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    MsgBox "Action Completed"
End Sub

 

[Fluff]

Another option

Sub CopyDel()

   With ActiveSheet
      If .AutoFilterMode Then .AutoFilterMode = False
      .Range("A1:H1").AutoFilter 8, "*L5P*"
      With .AutoFilter.Range.Offset(1)
         .Copy Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1)
         .EntireRow.Delete
      End With
      .AutoFilterMode = False
   End With
End Sub

 

[Deanname]

This almost works perfectly. However, it doesn't copy the entire row. It only copies columns A-H and not the rest of the columns associated with that row. Can you adjust accordingly?

I REALLY APPRECIATE IT!

 

[Fluff]

What is the last column?

 

[Deanname]

alansidman code is the one that works.

The last column is Q

Thanks!

 

[Fluff]

If you're interested this will copy cols A:Q

Sub CopyDel()

   With ActiveSheet
      If .AutoFilterMode Then .AutoFilterMode = False
      .Range("A1:Q1").AutoFilter 8, "*L5P*"
      With .AutoFilter.Range.Offset(1)
         .Copy Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1)
         .EntireRow.Delete
      End With
      .AutoFilterMode = False
   End With
End Sub