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]

Welcome to the MrExcel board!

..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.

IF your circumstances are identical to Desmondo's, apart from the sheet names, then all you should need to do is alter "ARCHIVE" in the code to "Completed - Orders".

However, you should note that
- Desmondo's data started in row 3 (rows 1 & 2 contained headings presumably)
- The post where I gave that code had assumption and referred to other assumptions in an earlier post. All assumptions would also need to be true for you for the code to work as expected.

Did the code error at all when you tried it?

Also is it possible to highlight the next available column when input is put into the one next to it?

I have no idea what that is actually asking or which sheet it is referring to. Be specific and give examples if possible.

 

[shejeer]

Hi Peter,

Please help me to sort out the below macro. I have a two sheet ‘Payment’ and ‘Not due’. The sheet ‘Not due’ having “due” items and “not due” items, and sheet’s heading is on 1st row (A1:V1). The purpose below macro is, it will go to ‘Not due’ sheet, then filter with “Due” items then paste into ‘Payment’ sheet (payment sheet heading is on 6^th row). The issue of below macro is, it also copying the heading of ‘Not due’ sheet into ‘Payment’ sheet. Also I need to delete the entire row of “due” items once it copied to ‘Payment’ sheet.

My macro

Sub Macro34()

Sheets("NOTDUE").Select
' Turn off autofiltering
If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False

' Turn it back on
Rows(1).AutoFilter

' Set the autofiltering conditions
Rows(1).AutoFilter Field:=22, _
Criteria1:="=DUE", _
Operator:=xlOr, _
Criteria2:="=DUE"

' Copy only the relevant range
Range("A1", _
Cells(65536, Cells(1, 256).End(xlToLeft).Column).End(xlUp)).SpecialCells(xlCellTypeVisible).Copy

' Paste the data into Sheet2 (assuming that it exists)
Sheets("PAYMENT").Range("A6").PasteSpecial xlPasteValuesAndNumberFormats
Application.CutCopyMode = True

End Sub

 

[shejeer]

Please helpe me Peter

Hi Peter,

Please help me to sort out the below macro. I have a two sheet ‘Payment’ and ‘Not due’. The sheet ‘Not due’ having “due” items and “not due” items, and sheet’s heading is on 1st row (A1:V1). The purpose below macro is, it will go to ‘Not due’ sheet, then filter with “Due” items then paste into ‘Payment’ sheet (payment sheet heading is on 6^th row). The issue of below macro is, it also copying the heading of ‘Not due’ sheet into ‘Payment’ sheet. Also I need to delete the entire row of “due” items once it copied to ‘Payment’ sheet.

My macro

Sub Macro34()

Sheets("NOTDUE").Select
' Turn off autofiltering
If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False

' Turn it back on
Rows(1).AutoFilter

' Set the autofiltering conditions
Rows(1).AutoFilter Field:=22, _
Criteria1:="=DUE", _
Operator:=xlOr, _
Criteria2:="=DUE"

' Copy only the relevant range
Range("A1", _
Cells(65536, Cells(1, 256).End(xlToLeft).Column).End(xlUp)).SpecialCells(xlCellTypeVisible).Copy

' Paste the data into Sheet2 (assuming that it exists)
Sheets("PAYMENT").Range("A6").PasteSpecial xlPasteValuesAndNumberFormats
Application.CutCopyMode = True

End Sub

 

[ggray50]

Hi. Just registered and look forward to looking through this great forum.

Got an issue right now though. I've set up an Excel workbook with a number of sheets and want to move items from my WORK sheet (which is a to-do list) to an ARCHIVE sheet (a separate sheet for completed tasks) so that it is removed from the former and added to the latter automatically - when a "y" is put in the J column (entitled "completed?") cells. I tried the code in post 6 but I get an error message - compile error: syntax error . When I click OK on the error message, it highlights the first line of the code in yellow with a little yellow arrow on the left of the 1st line of the code. Finally, when I close down the code window, I get another message saying this command will close the debugger. Not sure what any of that means, but I would be very grateful for any help you could offer.

As an aside, can you advise if macro enabled spreadsheets are compatible with a windows phone?

 

[Michele_Macaroncini]

Amazing solution, thanks a lot.
I have macro which stams the date next to "completed" status cell.
How should i adjust this macro to move completed rows e.g. after 2 weeks of completion date?
Thanks a lot in advance!

 

[NR1996]

Hi peter, I found this thread and it is very helpful. But I'm having the problem where I can't get it to run the code. I am having it copy each row that has the word "yes" in the column L in sheet named Locker A and then copy it to page Archive. Then I was wondering if it was possible if I could have the code clear the cells in that row that intersect with columns A, G, H, and L. Here is how I changed the code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Changed As Range
Dim lr As Long

Const YesCol As String = "L" '<- Your 'Yes' column

Set Changed = Intersect(Target, Columns(YesCol))
If Not Changed Is Nothing Then
Application.EnableEvents = False
Application.ScreenUpdating = False
lr = Cells.Find(What:="*", After:=Cells(5, 5), LookIn:=xlValues, _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious, SearchFormat:=False).Row
With Intersect(Rows("5:" & lr), Columns(YesCol))
.AutoFilter Field:=1, Criteria1:="=YES"
If .SpecialCells(xlVisible).Cells.Count > 1 Then
With .Resize(.Rows.Count - 1).Offset(1).EntireRow
.Copy Destination:= _
Sheets("Archive").Range("A" & Rows.Count).End(xlUp).Offset(1)
.Delete
End With
End If
.AutoFilter
End With
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End Sub