Leading '0's disappear after transfer by macro

[Guest]

I did a macro to transfer data from one file to another. But the leading zeros of one field always disappear after the macro is executed, even if I format the data in the source file as text and added with a ' in front.

What can I do to keep the zeros?

 

[BrianB]

I guess that you want to end up with numbers ? In which case you forma
the target cells with a custom number format like 000000000 o
whatever depending how many digits you have.

If you want text, format the target cells as text beforehand an
include something like this which converts text to numbers then back t
text :-

myvalue = "001234"
ActiveSheet.Range("A1").Value = _
Format(Val (myvalue), "00000")


If the source is a number, leave out the Val() bit

 

[Dave Peterson]

My bet is you did something like:

dim rng1 as range
dim rng2 as range

set rng1 = 'some worksheet's range
set rng2 = 'some other worksheet's range

rng2.value = rng1.value

This is like typing the value into the receiving cell. (and .value does not
include that leading single quote--called the PrefixCharacter.)

So it would be like typing 00001 into a cell. If the cell is formatted as
General, it'll lose the leading 0's.

You could either format the other range with the numberformat you like:

rng2.numberformat = "00000"


or just copy|paste (or copy|paste special|values)

rng1.copy _
destination:=rng2

or
rng1.copy
rng2.PasteSpecial Paste:=xlPasteValues