I have an 2003 Excel template.xlt with three worksheets, each saved with columns that have the following formats:

currency 0 decimal
number 0 decimal with "," separator
percent .2 decimal example -23.59%
borders
column width and height

My process is - export an 2003 Access query to Excel 2003 format.

Copy the data in nine columns and paste it into my template.

PROBLEMS:

When I "paste special" "All" into my template page it loses the currency, number, and percent formatting saved in the template. It also removes all light gray row borders.

The percent columns seems to be pasted in a text format as the little green triangle icon is in the top left hand corner asking if I want to convert the cell into a number format. This is in each percent cell.

Conditional Formatting (font color) and row and column dimensions ARE NOT changed when I paste the data into the template.

How do I prevent losing currency, number, percent and border formatting in the template.

Thanks

Rick

Hi Rick,

Try Paste Special > Values rather than Paste Special > All.
This should leave your formatting intact.

It also looks like your Access query is exporting some fields as strings rather than doubles, so that needs to be looked at.

Hope that helps...

Thanks Colin - Ill try that when I'm at the office tomorrow.

Rick

Thanks Colin. I was have the same basic issue and your solution worked like a charm.

I had a similar problem awhile back and could not figure it out for the life of me. This post would have helped.