Move Information from One Sheet to Another if a Cell Shows A Certain Value

Hello,

I am creating a spreadsheet to display the required "kits" that are needed for orders.
The sheet displays the item number and the base part required to create the kit.
It also shows the status of the operation so the requester has visibility.

What I am hoping to achieve is, when the cell in column F is changed to "Complete", the entire row is moved over to a separate worksheet (named "Archive" in the example sheet).
I have managed to use VBA to create a command that facilitates this; however, I have also encountered a couple of issues:
- I need the values only to move, not the formulas that apply to the cells
- I need to have the workbook protected so no users can accidentally delete or alter the formulas
- The command I have written would need to run automatically as soon as the cell is changed to "Complete"

I have been pulling my hair out trying to figure this out, being relatively new to these functions, so would welcome any solutions available.

Many thanks!

For starters a VBA event procedure to paste only to Sheet1 (Main Sheet) worksheet module :

PHP Code: 

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column <> 6 Or Target(1) <> "Complete" Then Exit Sub
    Application.EnableEvents = False
    With Range(Target(1, -4), Target(1)):  V = .Value:  .Delete xlShiftUp:  End With
    With Sheet2.[A1].CurrentRegion.Rows:  .Item(.Count + 1) = V:  End With
    Application.EnableEvents = True
End Sub 


► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

Originally Posted by Bernie Deitrick

This line will throw an error is Target isn't a single cell

Thanks, my post #3 already updated …

Bernie, to avoid your codeline may crash

Please Login or Register  to view this content.

you should use CountLarge rather than Count …

Hi!
In the example sheet, it's columns A, C and D that have the formulas that need to remain active.

Originally Posted by Mumps1

Start by unprotecting all the cells in columns A, C and D. If you need help with this, let me know. Then protect the Main Sheet using a password of your choice. Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your Main Sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Change the password in the code (in red) to match your password.
Close the code window to return to your sheet. Select "Complete" in column F.

Please Login or Register  to view this content.

Thank you, this has worked perfectly!

Originally Posted by Mumps1

Start by unprotecting all the cells in columns A, C and D. If you need help with this, let me know. Then protect the Main Sheet using a password of your choice. Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your Main Sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Change the password in the code (in red) to match your password.
Close the code window to return to your sheet. Select "Complete" in column F.

Please Login or Register  to view this content.

Sorry, one more question: I have another spreadsheet that I need to add this macro too as well; however, it has more columns (so the target column for the "Complete" status is different) and a different name for the sheet the info needs to be moved to.
Please may you confirm which parts I would need to amend to get the macro to work with the new sheet?
I thought I knew but I'm definitely missing something obvious.

Thank you again!