how2 keep #stored as text as #stored as text in macro variab

[theomountain]

How do I keep the format of my variable value in a macro as number stored as text

locn = activecell.value (the active cell shows 0023, number stored as text)
The column is formatted as text
with the macro I go to another sheet (same workbook), select a cell and
activecell.value = locn (this active cell shows 23)
this column is formatted as text

how do I get the value to show 0023?

Is there a VBA command extension that will force the value to stay the same

Thanks
Theo

 

[Jonmo1]

try
locn = activecell.text

and


activecell.text = locn

 

[VoG]

Try like this

Sub test()
Sheets("Sheet1").Range("A1").Copy
Sheets("Sheet2").Range("A1").PasteSpecial Paste:=xlValues
Sheets("Sheet2").Range("A1").PasteSpecial Paste:=xlFormats
End Sub