Moving rows to other spreadsheets based on cell value

[paullyv33]

I would like to create a macro so that when the value in any cell from C3:C5000 is "Dropout" in the spreadsheet "Hoopers", that the cell's corresponding row (including the cell itself) be cut/moved to the first available row in another spreadsheet labeled "Dropout."

Then, after that row is moved, the resulting empty row is automatically deleted.

I would appreciate any help you could offer. Thank you.

 

[texasalynn]

Welcome to MrExcel board....

Try this

Sub Transfer_data()
Dim i As Long, LR As Long, ALR As Long
Dim MFile As String, DFile As String
MSht = "Hoopers"
Dsht = "Dropout"
Sheets(MSht).Activate
LR = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
ALR = Sheets(Dsht).Range("A" & Rows.Count).End(xlUp).Row + 1
Application.ScreenUpdating = False
Application.DisplayAlerts = False
'filter for dropout data
    Cells.AutoFilter
    Cells.AutoFilter Field:=3, Criteria1:="Dropout"
    Range("C3:C" & LR).SpecialCells(xlCellTypeVisible).Select
    Range("A3:D" & LR).Copy Destination:=Sheets(Dsht).Cells(ALR, 1)
    Range("C3:C" & LR).EntireRow.Delete
    Cells.AutoFilter
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

 

[paullyv33]

Thank you, texasalynn.

Unfortunately it did not seem to work on my spreadsheet. I am unsure as to why...

The column that would have the value "Dropout" extends from C3:C5000 and the rows range from A:AM.

 

[texasalynn]

so is "Dropout" in the cell alone or is it with other words? (i.e. Dropout Now, Drop Later, etc)

 

[paullyv33]

The word "Dropout" will be in the only value in that cell.

 

[texasalynn]

Thank you, texasalynn.

Unfortunately it did not seem to work on my spreadsheet. I am unsure as to why...

The column that would have the value "Dropout" extends from C3:C5000 and the rows range from A:AM.

So maybe I need a little more information. What doesn't seem to work? Nothing happened, something happened but not what you want, Code gives an error? Please give more specific information

 

[paullyv33]

Sorry for the lack of details.

I copied your code into a VBA module, saved it, and when I returned to my spreadsheet and entered "Dropout" in any of the cells in column C, nothing happened.

 

[texasalynn]

Sorry for the lack of details.

I copied your code into a VBA module, saved it, and when I returned to my spreadsheet and entered "Dropout" in any of the cells in column C, nothing happened.

and you ran the macro?

 

[paullyv33]

I was able to run the Macro and it actually works extremely well. Thank you very much. Earlier, I had assigned the wrong Macro (not yours) to the button, which is why I could not get it to work.

My only issue now is that the same row that will move (the one with "Dropout" in column C), has values in it from A:AM. When I run your macro, the only values that transfer to the "Dropout" spreadsheet are from columns through D; the values from columns E to AM do not transfer.

 

[paullyv33]

*from columns A through D