cut and copy an entire row based on value in a cell

Hi,

I have a spread sheet containing a list of tasks. The first sheet is called "Press Shop", I also have a sheet called "archive". When a task is complete, column J is set to complete. I am looking for some code that will cut this entire row out of the "Press Shop" sheet, move the cells up, and copy it to the bottom of the list in "archive".

Can anyone help me please?

Code I have so far.

Sub As_Of_Analysis_Sorting()

Dim lr As Long
Dim lr2 As Long
Dim r As Long

lr = Sheets("Press Shop").Cells(Rows.Count, "A").End(xlUp).Row
lr2 = Sheets("Archived Changes").Cells(Rows.Count, "A").End(xlUp).Row

For r = lr To 2 Step -1
If Range("J" & r).Value = "Complete" Then
Rows(r).Copy Destination:=Sheets("Archived Changes").Range("A" & lr2 + 1)
lr2 = Sheets("Archived Changes").Cells(Rows.Count, "A").End(xlUp).Row
End If
Next r

End Sub

It's repeating some rows twice, and obviously not deleting rows yet


Thanks

Hi, michaelcouch87,

maybe like this

Please Login or Register  to view this content.

Ciao,
Holger

Hi, michaelcouch87,

you could call any of the macros from any event being raised in the Workbook (either ThisWorkbook: Workbook_Open, Workbook_BeforeSave, Workbook_BeforeClose, behind a Worksheet: Worksheet_Activate or Worksheet_Deactivate). This action would request that you save the workbook after the code is run.

Ciao,
Holger

Hi, michaelcouch87,

if the data is going through all columns you might like to try something like

Please Login or Register  to view this content.

If you want Date and Time inserted you might use Now instead of Date.

you might have a look at this thread to find hints on online material as well as books.

Ciao,
Holger