Range.Value = Range.Value, isn’t it?
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: (see comment below)
Sheet1.Range("A1:A100").Value = Sheet2.Range("A1:A100").FormulaLocal
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
Filed under: General on April 24th, 2007 by Bastien |
I just found out that in Excel 2003 you shouldn’t use the .value = .formulalocal. It is not always reliable. I wrote and tested the code above in Excel 97 and XP and that worked great. However in 2003 Excel somehow the values go wrong. Although I use the formulalocal, the decimal is seen as thousant separator. Even though I use the english version of Excel and have the same regionals settings in Windows.
After all I suggest you use the copy and paste method.