Macro to Move Cells to Archive Sheet

[SpacemanSpif]

Hi there, longtime user firsttime poster. Looking for some help as I am a non-expert with macros. Here's what I'm trying to do:

We have to submit things to a certain regulatory body and we usually enter tasks in as soon as they come, do the submission, and then keep a record of that submission.

So, I have a workbook with two sheets, one is "TO DO", the other is "ARCHIVE". Both sheets have the same columns and everything. I am looking for a macro that will automatically cut a (row) from the TO DO sheet and paste it in into the ARCHIVE sheet once it is done, then delete the cut row from the TO DO list so it stays topped up.

The trigger for archiving is the columns M and N which are titled "Complete ?" and each has a validation drop down that says "YES". When both cells in columns M and N have the YES in them, I would like the macro to make the above mentioned actions.

I ran a search on the forums and found something similar, but not quite what I was looking for.

Any help?

 

[Peter_SSs]

I want only move upto AE only

Welcome to the MrExcel board!

Untested, but try

With .Offset(1).EntireRow.Resize(, 31)

 

[shejeer]

Welcome to the MrExcel board!

Untested, but try

With .Offset(1).EntireRow.Resize(, 31)

Thank you, it is moving upto specified cell only instead of entire row; however while running the macro excel prompting a message "Delete entire sheet row?". please advice how i can avoid prompting this message.

 

[Peter_SSs]

"Delete entire sheet row?".

Do you want the entire sheet row deleted or only 31 cells of it??

 

[shejeer]

Do you want the entire sheet row deleted or only 31 cells of it??

Yes, I want to delete the entire row from the ‘TO DO’ list, but only want to copy upto 31^st column (AE) into “ARCHIVE” sheet.

 

[Peter_SSs]

Add this to the delete line

.EntireRow.Delete

 

[shejeer]

Add this to the delete line

.EntireRow.Delete

Thank you very much. This is working fine now ! perfect.I am a new user in mrexcel, this forum is really great and speedy, Peter you are great expert.

I am wondering if you can help me to one more requirement on the same file. With the help of the above macro I moved the data of A to AE from 'To Do' sheet to 'Archive' sheet. In 'Archive' sheet there is formula in AF column which calculate whether the line item is 'Due' or 'Not Due'. My requirement is when I run a macro from 'TO DO' sheet, it will go to 'Archive' sheet and filter the data with 'Due' under 'AF' column, then copy the data from A to AE range, then paste the same into 'TO DO' sheet from the cell A6 (i have heading upto A5), then delete the entire row filtered with 'Due' in 'Archive Sheet'. The number of 'Due' item vary from 0 to 50 or more (I.e sometime there is no 'due' items and sometime may be 1 row, 2 row and more)

Please help me.

 

[Peter_SSs]

I am about to sign out and won't be on the forum for a few days. I'll take another look when I can.

 

[Peter_SSs]

.. paste the same into 'TO DO' sheet from the cell A6 ..

What should happen to any data already in 'TO DO' sheet in rows 6, 7, 8, ... etc ?

 

[shejeer]

What should happen to any data already in 'TO DO' sheet in rows 6, 7, 8, ... etc ?

It will be blank. we can paste the due date from 6,7,8 and onwards.

 

[Bianca_Rose]

Thank you very much for this awesome thread. I am super new to VBA but do have a need. I have an Excel worksheet that I am building and would like it to have feature very similar to the thread I am replying to. I copied the below formula and it does disappear when I select Yes but I am unable to see where it went to. The Archive tab remains empty. Is it possible you can build something similar, the only difference is that my tabs are called Orders - when yes is selected in the completed column, it should go to the Completed - Orders tab. Also is it possible to highlight the next available column when input is put into the one next to it?

Assuming your column J is not the result of a formula, you don't need many changes from the code posted above. Try this in a copy of your workbook. Implementation instructions in post #3.
Assumptions in post #3 also need to be true - post back with details if they are not.


Private Sub Worksheet_Change(ByVal Target As Range)
****Dim Changed As Range
****
****Const YesCol As String = "J" '<- Your 'completed' column
****
****Set Changed = Intersect(Target, Columns(YesCol))
****If Not Changed Is Nothing Then
********Application.EnableEvents = False
********Application.ScreenUpdating = False
********With Intersect(ActiveSheet.UsedRange, Columns(YesCol)).Offset(1)
************.AutoFilter Field:=1, Criteria1:="=YES"
************With .Offset(1).EntireRow
****************.Copy Destination:=Sheets("ARCHIVE") _
********************.Range("A" & Rows.Count).End(xlUp).Offset(1)
****************.Delete
************End With
************.AutoFilter
********End With
********Application.EnableEvents = True
********Application.ScreenUpdating = True
****End If
End Sub