Move data (completed row) from one row on Tab 1 to Tab 2 using a macro

Hello there! Avid user of this forum, but never posted. Please help me out!

Heres the situation:
--> User makes a selection to change Cell S in Row 26 (for example) to a "4" or "Completed"
--> Conditional values are set to turn the entire row Grey so the user knows the item is complete but not archived
--> Make a button (macro) to find all values in column S that are labeled "4" and move the entire row to a tab labeled "Archive"
--> Delete the row when finished
--> Have this work for multiple rows that may all be complete at the same time, but not archived yet

I tried a few If-Then statements, but they archive the data immediately. I want the user to be able to archive the data when they are ready.

???

Thank you in advance!

Cainamogel82

1) This code works for another user with Cars and the Sold/Repaired macro. I can't get it to work in my book, but I also do not want the values to automatically move over, I want them only to move once the user selects the button.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column = 18 And Target.Value = "4" Then
Target.EntireRow.Copy Worksheets("Archive").Range("A" & Rows.Count).End(xlUp).Offset(1)
Target.EntireRow.Delete
End If
End Sub

2) It is in something labeled "Module 4"...not quite sure if that makes it unuseable.

3) I can make it available, but I would need to erase quite a bit. If the info above doesn't help, then I can work on creating a non-confidential copy.

I don't know, I was hoping to keep it simple...

However, in starting this thread, another thread popped up and it seems to have given me the code that works:

Sub UpdateCompleted()

Dim LastRowCompleted As Single, LastRow As Single, x As Single

Application.ScreenUpdating = False
Application.DisplayAlerts = False

LastRowCompleted = Sheets("Archive").Cells(65000, 2).End(xlUp).Row
LastRow = Cells(65000, 2).End(xlUp).Row

For x = 26 To LastRow
If Cells(x, 18).Value = "4" Then
Range("A" & x & ":T" & x).Select
Selection.Copy
Sheets("Archive").Select
Range("A" & LastRowCompleted + 1).Select
ActiveSheet.Paste
LastRowCompleted = Sheets("Archive").Cells(65000, 2).End(xlUp).Row
Sheets("Open").Select
Rows(x & ":" & x).Select
Selection.Delete Shift:=xlUp
End If
Next x

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

So, I think I solved my problem (or basically, this thread solved my problem: http://www.excelforum.com/excel-prog...ro-button.html)

Thank you!

Cainamogel82

Which...I just noticed is also you.

Okay...I'll give you a thumb dude, thank you!

And also noticed I can't give you good reputation multiple time sin a short period...

Reputation.jpg