Validation drop-down menus disappear in Excel 2002 w/o frozen panes

[Stuckfly]

Using Excel 2002 with SP2 installed, in Windows XP Pro.

After copying a range and pasting it elsewhere in the same sheet all
validation drop-down menus vanished. Panes are unfrozen. Other
sheets in the same workbook are unaffected.
Tools/Options/View/Objects is set to Show All. Closing the file and
Excel, then reopening the file is no good, nor is rebooting the
machine. There are merged cells in the sheet, and some of them
contain validation menus, but drop-downs are missing even in rows
without merged cells. Unmerging all cells in the sheet does not
restore the menus.

I found one other thread about this (or similar) problem using Excel
2000, but it remains unanswered. All other relevant hits seem to
apply only to older versions, esp. 97. Anybody seen this in XL2002,
or know a non-VBA solution?

 

[JE McGimpsey]

Yes, pasting wipes out any validation set for the cells pasted into in
all XL versions.

No non-VBA workaround that I know of.

 

[Stuckfly]

Unless anyone else has a better solution, here's what I came up with
since posting the original problem:

Copying the sheet does no good, but making a new sheet and copying the
range of used cells to the new one does (or did this time) restore the
validation menus. I had to copy/paste formats for columns and rows
(separately), too. Then redo page setup.

In the process I unburied another odd glitch, which is that the old
sheet was adding and subtracting some cells with #VALUE!, but it *was*
accepting them as zero (these were not sum functions, just operators).
This is not supposed to happen, the results should have been errors!
The new sheet evaluated them to errors without any edits, so maybe
there's a connection between the two glitches? I replaced the
formulae with sum functions and they work fine now.

Before executing the *exact* same process on the new sheet that
mangled the old one, I copied the new one as a backup. Oddly, the
problem did not reoccur. Go figure.