automatically cut and paste from a table to another worksheet based on the value in a column

[tbrynard01]

I have a table that checks the data and if the client has discharged it puts the word discharge in column O. I would like to find a way to automatically move all the discharged clients from my active worksheet (MHRData) to another sheet called ArchiveData. Any help would be appreciated. I want the data deleted from the MHRData sheet. Thank you

 

[My Aswer Is This]

You said:

I have a table that checks the data and if the client has discharged it puts the word discharge in column O

Tell me how does this happen.
Do you manually enter discharge in column O

If not how is discharge entered in column O

And do you want this row removed from the sheet named:

MHRData​

​

 

[tbrynard01]

I use a formula that checks a census worksheet and puts discharged if the client is no longer on census

Yes I want this row removed from the MHRData sheet.

Thanks

 

[My Aswer Is This]

I use a formula that checks a census worksheet and puts discharged if the client is no longer on census

Yes I want this row removed from the MHRData sheet.

Thanks

I can write a script to do this but you said:

find a way to automatically move all the....

Something has to occur for this script to run.

Like you may need to click a button or double click a cell or manually do something.

A Excel script in most cases cannot just run automatically.

So would clicking a button work for you?

You click a Button to run the script and all rows with "

discharged" in column O will be copied to other sheet and rows from original sheet are deleted. Would that work for you?​

​

 

[tbrynard01]

Yes thank you

 

[My Aswer Is This]

Try putting this script in a button on the current sheet with your data.

See below post for code:

 

[My Aswer Is This]

Sub Filter_Me_Please()
'Modified  11/18/2019  9:44:20 PM  EST
Application.ScreenUpdating = False
Dim lastrow As Long
Dim lastrowa As Long
Dim c As Long
Dim s As Variant
c = 15 ' Column Number Modify this to your need
s = "discharged" 'Search Value Modify to your need
lastrow = Cells(Rows.Count, c).End(xlUp).Row
lastrowa = Sheets("MHRData").Cells(Rows.Count, c).End(xlUp).Row + 1

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("MHRData").Rows(lastrowa)
        .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

 

[tbrynard01]

Thank you so much. Really appreciate the help.

 

[My Aswer Is This]

Thank you so much. Really appreciate the help.

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

 

[tbrynard01]

Hi, I was finally able to work on this and I'm getting the error message "no value found" but I have several rows with "Discharged" in that column.

Also don't see where it's pasting it in the ArchiveData sheet?

This is the code I put in the Command button - I'm sure I'm missing something basic:

Private Sub CommandButton1_Click()

Application.ScreenUpdating = False
Dim lastrow As Long
Dim lastrowa As Long
Dim c As Long
Dim s As Variant
c = 15
s = "Discharged"
lastrow = Cells(Rows.Count, c).End(xlUp).Row
lastrowa = Sheets("MHRData").Cells(Rows.Count, c).End(xlUp).Row + 1

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("MHRData").Rows(lastrowa)
.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

Thanks.