PLEASE HELP... trying to move a row to a archived sheet when a certain cell is marked YES and delete it from the main sheet once moved

[kheiple]

I really need help with this, I have tried for 3 days now and cannot figure it out. I have a spreadsheet named "Regular Call Backs" which contains several rows of data (A-O) I need to move a entire row to "Archived" sheet when column O is marked YES. I also need to delete the row when it is moved to "Archived". On this same sheet I already have it auto sorting entries based on a date and I need to keep this as well. ANY HELP SO VERY GREATLY APPRECIATED!! I have posted on other forum's and received the response study VBA. Which was no help at all I have been trying to figure this out for 3 days now and the only result I am getting is totally messing my spreadsheet up and causing me to have to redo it to only try again with the same result

 

[codeliftsleep]

When are you wanting it to be moved? When it gets opened?

 

[kheiple]

Moving when opened would be perfect!!

 

[codeliftsleep]

Moving when opened would be perfect!!

Ok give me a few hours and I'll have something for you...on a smartphone now...when I get home I'll write something up in excel and paste the code for you.

 

[codeliftsleep]

Copy This code to the ThisWorkbook module and it should run everytime the workbook is opened.

Private sub Workbook_Open()
Dim Main, Archive as worksheet
Dim archiveRow, mainRow as long

Set Archive= Sheets("Archive")
Set Main= Sheets("Main")

archiveRow= Archive.Range("A:A").Find(What:="*", searchDirection:=xlPrevious, LookIn:=xlValues)

MainRow= Main.Range("A:A").Find(What:="*", searchDirection:=xlPrevious, LookIn:=xlValues)

With Main
.Autofiltermode= False
.Range("A2:O" & mainRow).AutoFilter Field:=15, Criteria1:="=Yes"
If .Range("A2:O"& mainRow).SpecialCells(xlCellTypeVisible).Rows
Count>1 then
.AutoFilter.Range.Copy Archive.Range("A"&archiveRow)
End if
End With
End sub


Sorry about the formatting, no internet where I am at had to type on my smartphone.

 

[L. Howard]

If you want to do the copies and deletes 'as you view the sheet' you could do this.

Copy code to a standard module.
On the sheet Regular Call Backs assign code to a button or shape on the sheet and click on it to run.

Howard

Sub cell_is_marked_YES_and_delete()
Dim LstRw As Range
Dim lRow As Long

Set LstRw = Sheets("Archived").Cells(Rows.Count, "A").End(xlUp)(2)
lRow = Cells(Rows.Count, "O").End(xlUp).Row

With Sheets("Regular Call Backs")

 .AutoFilterMode = False
  ActiveSheet.Range("$A$1:$O$" & lRow).AutoFilter Field:=15, Criteria1:="Yes"
  Range("A2:O" & Cells(Rows.Count, "A").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy LstRw
  Range("A2:O" & Cells(Rows.Count, "A").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Delete
 .AutoFilterMode = False

End With

End Sub

 

[kheiple]

I am getting a error trying on both of the codes shared here when I try them. I have been trying to get this figured out for days Im going to share a link to the file Im trying to do this to. I have removed all personal info. Any help would be soooo much appreciated!!
1. I have Regular Calls Backs sheet auto sorting by date as one is entered to a row.
2. I have VBA assigned to the "envelope icon" which is auto sending email reminders.
I need to keep these working as well
Just need Regular Call Backs to auto move a entire row A-O based on column O "YES". My data does not begin until row 4 as the other 3 are dedicated to my headers.

https://1drv.ms/x/s!AiV-IZ5b7K_6hEas7sDWsREdi6tV

 

[L. Howard]

The linked workbook is very crowded and cluttered with text boxes and headers, etc., very difficult to work with. A number of A to O columns are hidden...?

Here is an uncluttered two sheet example to do the "Yes" in column O transfers to the Archives sheet, then deletes the filtered visible cells. You will get an alert message asking you if want to delete all sheet rows, click Yes to delete all the Yes rows showing and the remaining data will then be restored in place.
I left three rows for headers and the code is written with those three header row taken into consideration.

The phony data goes about 300 rows, I have tested it on 800 +- rows with about 500 Yes entries in column O. Works in just over a blink in time to filter and transfer and produce the alert message.

You can also click Cancel and prevent deleting the Yes rows. The Archive sheet will still have listed the Yes rows, so you can do what you want with them after clicking Cancel.

So, you would put any number of yes's in column O, then click the blue shape. It has the code assigned to it.

https://www.dropbox.com/s/q8ubq0m4tm0i09c/Regular Callback Demo.xlsm?dl=0

On your linked workbook you have placed the Workbook Open macro in a standard module where it should go in the ThisWorkbook module. And you also have Change_Event macros in standard modules where they must be in the sheet module for which intend them to work on/for.

And you could consider a simple macro like this to enter the Yes on column O instead of all the drop downs.
I have the key strokes Ctrl+Shift+Y assigned to it. (you may need to re-assign ctrl - shift - Y, not sure it will come with the drop box link)

Sub Cell_Is_Yes() 'Ctrl + Shift + Y
If Intersect(ActiveCell, Range("O4:O400")) Is Nothing Then Exit Sub
ActiveCell = "yes"
End Sub

Howard