Insert cut row in protected range of rows

I need help.

I need a VBA code for a worksheet to automatically insert cut paste a row from a range of unprotected rows to the range of protected rows once the user decides to do so.

I have a sheet called “Customer Stock” which has two tables called “Uncollected” and “Collected”.

I need “Uncollected” table to be unprotected and “Collected” Table to be protected.

Once the a Customer collects the goods, the user can cut and insert the collected row from the unprotected “uncollected” table to just above the first row of the protected “collected” table,

so that the inserted cut row now should be protected.

The password should be 1234 and the user won’t know the password. Only the administrator will be having it.

Yes....somebody suggested the same thing and wrote macro for me. It works really great except that

1. I can move only one row at a time instead of moving certain rows (eg R15,R18 and R22) from "Customer Stock" sheet to "Collected Stock" sheet.

Or better still, is it possible, if once i put the date in the "Invoice Posting Date" column, it should automatically trigger the "move" message box ?

2. The moved row is placed on the bottom instead on the top.

can you help me to improve the macro ?

Open the attached file, select any row in the "Customer Stock" sheet and then run the macro (ctrl + m),

It will move the selected row from unprotected "Customer Stock" sheet to the protected "Collected Stock" sheet

Sub Customer_Stock()
Dim drc, dx As Long

Application.ScreenUpdating = False
Application.DisplayAlerts = False
response = MsgBox("Are you sure you want to move the selected row from Customer Stock to Collected Stock?", vbYesNo)
If response <> 6 Then
MsgBox "No row moved!!"
Exit Sub
Else

Worksheets("Collected Stock").Unprotect Password:="a27826" ' change the password to whatever you wish
Sheets("Customer Stock").Select
dx = ActiveCell.Row
Rows(dx).Select
Selection.Cut
Sheets("Collected Stock").Select
drc = Range("A" & Application.Rows.Count).End(xlUp).Row + 1
Rows(drc).Select
ActiveSheet.Paste
Columns.AutoFit
Range("A1").Select
Worksheets("Collected Stock").Protect Password:="a27826" ' change the password to whatever you wish
Sheets("Customer Stock").Select
icstmr = Worksheets("Customer Stock").Cells(dx, 2).Value
Application.ScreenUpdating = True
Application.DisplayAlerts = True
MsgBox icstmr & " the selected row has been moved to Collected Stock"
End If
End Sub

Moderator Note:

I added code tags around your code. Pls next time to it yourself according the forum rules.

Thanx for replying

Once I enter something in the Invoice column, the sheet just blinks once and nothing else happens......no error messages......nothing.

Can you please look into it ?

eagerly awaiting your reply.

Hi
It works perfectly my end. Try noting which Number row from Column A you are entering a value in the Invoice Column for. Then enter something in the Invoiced column. Then move to the next sheet and you should see that row number at the top of the sheet.
The "blink" you are seeing is be cause it is transferring the relevant row to the Collected sheet.
I can put a message box in the program to advise which number transaction has been moved if that would help.
Let me know.
Tony

Hi Tony,

I found the problem, the column K (Invoice Posting Date) is "YES" sensitive because of [If actCell = "YES" Then] in the VBA code.

if I enter YES in capital, then the row immediately moves to the top row of the "Collected" sheet.

Thank you very much....you used your time and knowledge in helping me out.

Now as you know, a human being is never satisfied, always wanting more and more.

I request you further help me in improving the VBA code.

I need the column K (Invoice Posting Date) to be "date" sensitive instead of "YES" sensitive because of future reference i will need dates of collection rather than "YES"s.

Once again thank you for your kind help.