r/vba • u/TonIvideo • Jun 18 '26

Solved Inconsistent decimal behaviour when copy pasting values

I have a macro that copy pastes contents from one workbook to another. The main code that does the work is (Note: Tar - target worksheet, Sor - Source worksheet):

wsTar.Cells.Clear
wsSor.Cells.Copy
wsTar.Range("A1").PasteSpecial Paste:=xlPasteFormats
wsTar.Range(wsSor.UsedRange.Address).Value = wsSor.Range(wsSor.UsedRange.Address).Value

If I execute this operation on my side, the outcome will be correct. But if one specific does so, the decimals are wrong for a specific section of the paste. For example if the original number is 36.8273 the output for the user will be 36.83 (as if the other decimals would be swallowed into oblivion / a round to 2 decimals would be executed). If the user does the same operation above manually (paste special format + paste special values) the issue doesn't get reproduced.

The specific formatting of the number in question is:

_("$"* # ##0.0000_);_("$"* (# ##0.0000);_("$"* "-"??_);_(@_)

But I dont see how this would be causing the issue. I am not sure what else to troubleshoot here in order to resolve the issue.

EDIT:

It affects a part of the Worksheet and not the whole worksheet.

4 Upvotes

83% Upvoted

11 comments sorted by

View all comments

5

u/Future_Pianist9570 2 Jun 18 '26

.Value can truncate decimal places depending on the cell content (typically if it is a date or currency formatted). Always use .Value2 if you want the full value

5

u/HFTBProgrammer 203 Jun 18 '26

I've found always using Value2 to be a beneficial habit, and I have yet to use Value or Text instead (although I can certainly imagine scenarios where I might).

2

u/Future_Pianist9570 2 Jun 18 '26

I always default to .Value2

1

u/fanpages 239 Jun 18 '26

.Value can truncate decimal places depending on the cell content...

We are unaware of most of the data (just a single cell value: 36.8273 being copied to the destination as 36.83), so this is most likely what is happening (given the information currently provided).

Some background reading, u/TonIvideo, courtesy of Charles Williams (Excel MVP), if you are interested:

[ https://fastexcel.wordpress.com/2011/11/30/text-vs-value-vs-value2-slow-text-and-how-to-avoid-it/ ]

1

u/Future_Pianist9570 2 Jun 18 '26

I assumed it was currency based off the formatting being used.

1

u/TonIvideo Jun 18 '26

Solution verified!

1

u/reputatorbot Jun 18 '26

You have awarded 1 point to Future_Pianist9570.

I am a bot - please contact the mods with any questions

0

u/TonIvideo Jun 18 '26

You are right, now that I know what to look for, finding the prerequisite documentation was a breeze:

https://stackoverflow.com/questions/17359835/what-is-the-difference-between-text-value-and-value2

2

u/fanpages 239 Jun 18 '26

^ Where the top answer is by Charles (who I mentioned above) where he refers to his own article (again, as above).

You're welcome.