Copy a worksheet to another workbook as values but without changing the format of original results

[David Montoya]

Dear, I have a worksheet, which contains many formulas; I need to copy this worksheet to a separate workbook without the formulas (value only). The problem I am facing is that by using the macro below, it changes results such as 210.000 to 210, and I need to maintain the original result as 210.000.

Sheets("Std").Copy
With ActiveSheet.UsedRange
    .Value = .Value
End With

 

[Joe4]

Welcome to the Board!

it changes results such as 210.000 to 210

The formula that is returning this "210.000", is it a hard-coded entry or a formula?
Either way, I am guessing that you have a text value there that you are converting to numeric with your code, as the code above does not alter the formatting on the cells.
So as it converts from text to number, the decimals are being dropped.

 

[David Montoya]

Yes, it is a text result from a formula. I need to maintain the same format in order to not drop the decimals. The code I need is to maintain the same format/information. When I debug the code, the portion ".Value = .Value" is the one that does the conversion from 210.000 to 210. Is there a way to modify this portion of the code to retain the text values; but at the same time to eliminate the formulas from the source worksheet?

 

[Joe4]

Please post your formula.

 

[David Montoya]

Here is the formula: "=IF(Q4="","",IF(VLOOKUP($Q4,FDA!$A:$F,4,0)=0,"",VLOOKUP($Q4,FDA!$A:$F,4,0)))". It is a query from an Excel DB, which contain the mentioned value of 210.000 as text (many other similar values exist, all with three decimal zeros).

 

[Joe4]

If all the numbers have three decimal places, then just format the columns to numbers with 3 decimals BEFORE running your VBA code.
Or, simply add VBA code to the top that does the formatting for you. If you turn on the Macro Recorder, and record yourself applying the format, it will give you the code that you need.