VBA validation allow users to only copy to a specified range

[lynnsong986]

Hello,

I'm setting up a valuation model where each statement line item has a number of potential drivers for projection purpose. for example, revenue has "% of Growth from Prev. Year", "Scenario driven", "Volume Change" in the validation list. I need to set things up so that the validation rule only applies to say Range("J12: G20") as only these are related to revenue (COGS would have a different input range, for example). When a user select an item from the validation list and if he/she wants to copy it to the other input cells (so he/she doesn't have to click on the cell and pick a value twenty times), I need to add some codes to limit the copy paste only to range J12:G20 so that the user doesn't incidentally copy this to other statement line input ranges. How can I do this?

just to clarify, this is different from not allowing pasting from another cell without drop-down in it. this is ALLOWING copy/paste but only to specified ranges or ranges with exactly the same drop-down values. I think this would be a change event after copy before paste (go check the cell selected for pasting, if it is outside of J12:G20, throw an error message and terminate the paste)

is this doable?

thanks so much

[Winon]

Hello lynnsong986,

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[lynnsong986]

Hello lynnsong986,

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Thanks for replying. I decided to go another route with it. I protected the sheet but allow macros to select the entire range with the same value from the drop down using me.protect user interface only= true.

If this doesn’t work, I’ll attach a copy of the workbook.