Moving Data (entire rows) from one Sheet to Another Based on Criteria

[DOC1021]

Hello, I have a very similar issue to another older thread that was posted here a couple years ago.

I was hoping to get some help coding a macro to move data from one sheet to another and delete from the original sheet. In my spreadsheet I have these columns:

A B C D E F G H I
GC Project Name Location Material Date Time Status Estimator Notes

What I was hoping to have happen is any rows in the Status column with "Submitted" in the cell to be moved to the other sheet and deleted from the first sheet. For example G3 and G7 have Submitted in their cells, and would therefor be moved to the second sheet, and deleted from the first sheet once the macro was run. My first sheet is called Commercial_Bid_Tracking and my second sheet is called Submitted_Jobs

Below is the code that Michael M had written in 2017 and seems very close to what I need, with a couple adjustments.

Sub MM1() 'no header row
Dim lr As Long
If Application.WorksheetFunction.CountIf(Range("D:D"), "Yes") = 0 Then Exit Sub
Application.DisplayAlerts = False
lr = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row 'change sheet name to suit
With Sheets("Sheet1").UsedRange
.AutoFilter
.AutoFilter field:=4, Criteria1:="Yes"
.Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet2").Range("A" & lr + 1)
.Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Delete
.AutoFilter
End With
Application.DisplayAlerts = True
End Sub

I tried running this code with what I thought needed to be adjusted and couldn't get it to work because I have very little knowledge in coding. Any assistance with this would be very much appreciated. Thanks in advance!

 

[My Aswer Is This]

Try this:
A Inputbox will ask you what value to search for in column G
And in the script you will see sn modify this to name of sheet you want rows copied to.

Sub Filter_Me_Please()
'Modified  10/13/2021  1:21:46 PM  EDT
Application.ScreenUpdating = False
Dim lastrow As Long
Dim C As Long
Dim s As Variant
Dim sn As String
sn = "Alpha" 'This is name of sheet to copy row to. Modify to your needs
C = 7 ' Column Number Modify this to your need
s = InputBox("Enter search for value ")

lastrow = Cells(Rows.Count, C).End(xlUp).Row

With ActiveSheet.Cells(1, C).Resize(lastrow)
    .AutoFilter 1, s
    counter = .Columns(C).SpecialCells(xlCellTypeVisible).Count
    If counter > 1 Then
         .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets(sn).Cells(2, 1)
        
        .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    Else
        MsgBox "No values found"
    End If
    .AutoFilter
End With
Application.ScreenUpdating = True
End Sub

 

[DOC1021]

Thank you for responding so quickly!

I put in the correct sheet name for "Alpha" and column 7 was already correct.

When I got to the Input box I typed submitted and "Submitted" and both times I came up with the 1004 runtime error. The picture attached shows where the code apparently stopped working?

 

[My Aswer Is This]

Thank you for responding so quickly!

I put in the correct sheet name for "Alpha" and column 7 was already correct.

When I got to the Input box I typed submitted and "Submitted" and both times I came up with the 1004 runtime error. The picture attached shows where the code apparently stopped working?

So did you enter Just one word or more then one.
Show me exactly what you entered.
You must enter like: Alpha not Alpha& Bravo

 

[DOC1021]

So I renamed the macro to the name you had. (1)
The worksheet I'm working on. The only cells that have words in them is column G and the one I'm trying to base everything off of. (2)
The error message. (3)
After Debug (4)

Trying to go from the sheet named Commercial_Bid_Tracking to Submitted_Jobs shown in the second picture