Allowing pasting without overriding Validation

defreeze

New Member
Joined
Sep 7, 2006
Messages
2
I have a very large spreadsheet used for data entry at multiple research sites, and it is full of almost every kind of validation. I am making some major changes and updates to the conditional formatting as well as the validation (for instance, all missing data codes are being changed from '999' to '9999').

I need to paste all the old data (enormous) into the new version, but it is obviously causing the validation to revert back to version 1 because of the pasting. I know there is a way to "lock" the validation by disabling the paste function with code, but I need to be able to use paste. Why is there no way to simply paste the actual values without carrying the validation with it?? If I have to reenter the data manually cell by cell it will literally take me one year.

Is anyone able to help me with locking data validation, or suggest some other way to paste values into the cells without reverting to the previous validation?
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi, defreeze
Welcome to the Board !!!!!

did you try this
copy the date
rightclick
select "pastespecial"
check "values"

kind regards,
Erik
 

defreeze

New Member
Joined
Sep 7, 2006
Messages
2
THANK YOU ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! :pray: :pray: (y) :p :p :p :) :wink: :cool: :LOL: :oops: :oops: :oops: (y) :pray: :pray:

I feel like an idiot now, but you have no idea how much you've helped me!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,361
Messages
5,635,812
Members
416,884
Latest member
leeshjay

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top