Curious operation of Paste

[Fred Smith]

I have a spreadsheet with a column of numbers. The numbers actually represent a
percentage, but they're stored as a number (eg, 5.6, not .056).

Rather than divide all the numbers by 100, I decided to get cute and applied a
custom format of #,##0.0"%". Visually, it looked great.

In my macro, I filter the data, copy the visible cells and paste to a new
spreadsheet, planning on dividing by 100 every time I used data from this
column. Guess what happens? Paste actually changes the data to a real
percentage. 5.6 from the original cell is changed to .056, with a format of
#,##0.0%!

More of the story: don't use custom formats to display percentages. Store your
data properly and use a percentage format.

 

[Dave Peterson]

I just tried this in xl2003.

The value pasted fine (no change)--both manually and via code. But the format
was pasted, too--so it still looked like a percentage.

You may want to try paste|special|values to see if that helps you.