Insert cut row in protected range of rows

[a_27826]

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.

[ARGK]

Hi
Would it not be easier to have the collected items on a separate sheet. Then have a column that indicates the customer has collected which when populated triggers an automatic macro that cut and pastes that row on to the Protected Collected sheet.
If this helps let me know and I will write the macro for you.
Tony

[a_27826]

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

Please Login or Register  to view this content.

Moderator Note:

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

[ARGK]

Hi
Please find herewith my solution for your testing.
The password to unlock the Customer Collected sheet is "password" and can be changed to suit. If you change it you will need to change it also in the VBA Module in two places.
I have also passworded the VBA Module so the user cannot just open the VBA Module to view the password. The password for this is also "password" and can be changed to suit. If you change it you will need to save and close the Workbook and reopen it to activate the new password.
Once anything is entered in the Invoice column the row is automatically removed and pasted at the top of the Customer Collected sheet.
If you need any further changes then please come back to me.
Good luck.
Tony

[a_27826]

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.

[ARGK]

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

[a_27826]

maybe the attached file has a problem while uploading.

can you delete the uploaded file and attach again.

or download your own attached file to see if it has a problem.

sorry to give this trouble but i really need the file.

[ARGK]

Hi
It may be a version issue. I have saved it as a 2003 version so see how you get on with this one.
Tony

[a_27826]

its really frustrating.

lets give one more try.........

i am attaching the file which i have downloaded (the one which is not working).

Can you please download and see the problem ?

[ARGK]

Hi
Check your private messages.
Tony

[ARGK]

Hi
Check your private messages.
Tony

[a_27826]

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.

[a_27826]

I did try by replacing

If actCell = "YES" Then

with

If actCell = Date Then

But it works with the current date only

[a_27826]

Solved

i replaced

If actCell = "YES" Then

with

If actCell <= Date Then


Tony, once again thank you..

do i have to delete uploaded files ? if yes, then how do i do that ?

[ARGK]

Hi
Thanks for the feedback.
You don't have to delete uploaded files.
Tony

[a_27826]

i have come back for a help with almost same file.

initially i was able to cut and paste a row from the protected sheet "Customer Stock" to the protected sheet "Collected Stock" by putting a date in column "F".

now i am unable to do so till i unprotect the sheet "Customer Stock"

there must be something i did to change the behavior but i dont know what.

Can somebody please help me ?

I am attaching the file and its password is "a27826" without quotes.