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

New Member
Joined
Dec 22, 2017
Messages
4
:confused: I really need help with this, I have tried for 3 days now and cannot figure it out. :mad: 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 :crash: result
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
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.
 
Last edited:
Upvote 0
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

Code:
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
 
Upvote 0
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 :crash: 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. :confused:

https://1drv.ms/x/s!AiV-IZ5b7K_6hEas7sDWsREdi6tV
 
Upvote 0
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)

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

Howard
 
Last edited:
Upvote 0

Forum statistics

Threads
1,224,828
Messages
6,181,217
Members
453,024
Latest member
Wingit77

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
Back
Top