There are several ways to copy information from one sheet to another. Today I discovered an inconsistancy while using something similar like the following code:
Sheet1.Range("A1:A100").Value = Sheet2.Range("A1:A100").Value
The above code does not work correctly if the cells in sheet 2 are formatted in currency- or accounting format. In that case Excel does not copy the original values, but instead the values rounded as they are formatted. In my case amounts such as 12,213045 were copied as 12,21 which led to incorrect results. I figured out what was the problem pretty fast but I'm still wondering why Excel does this. It does not round the numbers if they are formatted as decimals.
Anyway I changed the code into the following which copied the numbers correctly including all its decimals:
Sheet1.Range("A1:A100").Value = Sheet2.Range("A1:A100").FormulaLocal
(see comment below)
For your information, I choose the above code for speed in stead of something like the following code:
Sheet2.Range("A1:A100").Copy
Sheet1.Range("A1:A100").PasteSpecial xlPasteValues
Application.CutCopyMode = False
Update September 2009
Instead of using .Value you can use .Value2 so the code becomes:
Sheet1.Range("A1:A100").Value2 = Sheet2.Range("A1:A100").Value2
That works correct. More information about the .value2 property is on the MS website.