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

DOC1021

New Member
Joined
Oct 13, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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!
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
18,160
Office Version
  1. 2013
Platform
  1. Windows
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.
VBA Code:
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

New Member
Joined
Oct 13, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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?
 

Attachments

  • VBA Code.jpg
    VBA Code.jpg
    104.5 KB · Views: 6

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
18,160
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
Oct 13, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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
 

Attachments

  • New VBA Code.jpg
    New VBA Code.jpg
    104.8 KB · Views: 10
  • Worksheet.jpg
    Worksheet.jpg
    59.5 KB · Views: 8
  • Error.jpg
    Error.jpg
    133.2 KB · Views: 9
  • After Debug.jpg
    After Debug.jpg
    120.2 KB · Views: 11

Forum statistics

Threads
1,147,962
Messages
5,744,060
Members
423,843
Latest member
alex2022

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
Top