VBA-Macro Prevent Copy Paste in Data Validation Cell

[Morshed_Dhaka]

Hello Everyone, I am facing a critical problem which I believe require macro to solve this problem. I tried to create the macro so many times but it is not working and not solving my problems. Really appreciate is some body helps me to solve this problem

In a worksheet I implied data validation rules within the cells "E7:E12". Data validation list data is already available in the file . When I select the data from drop down, it works perfectly. Also, If I want to type any data which is not available in the drop down menu it stops me and showing me error message. It also works fine.

But when I copy-paste-value paste data in those data validation cells from any other location, it captured the data and doesn't imply the data validation rules.

I am looking for a macro that will restrict user to copy/paste/value paste any data from anywhere except the data which is already available in data validation list. The macro should be applicable for only selected range. In this case the range should be "E7: E12". In addition, if it can be done that if someone copy paste data from other location which has mixture of available & non available data & when user copy paste it, only available data will be taken and non available data will be automatically deleted with warning message box.

I am looking for expert help in this regard. I will ever grateful if someone able to give me the solution. Thanks.

 

[aboly8000]

Without having access to the data file, and knowing exactly the range you are trying to copy, I really have no idea.

I'm trying to copy a lot of information in the dropdown list in column E
My range:
rng1 = Range("E:E")

 

[Joe4]

I am sorry, but without the file and the details of EXACTLY what you are trying to copy, I don't think I can help further.