The functionality for copying and pasting cells with Data Validation rules that specify cells on a different worksheet as their source was broken in Microsoft Office’s March 25th update, version 2002, build 12624.20320. The prior build, 12527.20278, does not exhibit this behavior. (Cells with data validation on the same worksheet can still be copied over.) This is not a Narrative1 issue - this behavior will occur whether using Merge Workbooks, copying and pasting the cells themselves, or using the “Move or Copy” function on the worksheets in question. At present, the only fix for this issue is to roll back one’s Office version to 12527.20278 or earlier. To make this easier, I’ve made a “rollback package”.
-
Download the "rollback.zip" file linked below, place it somewhere you can access it, and extract it. You should be able to just double-click it to open it, then click and drag the files out into a new folder of your choosing. If you can't open the file, you may need to install a program that can handle .zip files, like 7zip or WinRAR.
-
Double-click the "rollback.bat" file to run it. (It may just show as "rollback" if you don't have file extensions visible.) If it doesn't seem to run, you may need to right-click it and select "Run as Administrator".
-
An orange setup notification should appear briefly, then disappear. After this, open Excel or any other Office program. Make sure any open Office documents are saved.
-
Click the File menu, then click "Account" along the left/near the bottom. On the right, click "Update Options", then "Update Now". The update process will begin. It will at one point ask you to click Continue so it can close all your open Office programs. These will reopen, but you'll want to be sure you've saved any open documents.
-
You will be notified when the process is complete. At this point you should be on build 12527.20278.
-
To prevent automatic updates, you can go back to Update Options (per step 5) and select "Disable Updates". Otherwise, Excel should eventually detect and, at some point, acquire, the next update when it is released.
Returning to normal updates with Office 365 appears to require running an Online Repair. (If it is installed as a standard Windows program, open appwiz.cpl from the start menu, right-click Office 365, select Change, and select Online Repair) Note that one may possibly need to re-input Office credentials after an Online Repair.
Microsoft support has stated this issue is fixed in version 2006 build 13001.20266, which at time of writing should (on knowledge and belief) be available in July of 2020.