VBA: preceding zeros dropped when copied over

Question

I am creating a copy of an Excel file using VBA. In the file, there is a column that includes numbers with preceding zeros. The copy of the file is created, but the data in this column is dropped. I need to keep the values with the preceding zeros. How can I resolve this problem with VBA?

Answer 1

The best way is to pre-format the column as Text by setting Range.NumberFormat to "@". This way, if a user edits the cell, the cell will stay as text and maintain it's leading zeros. Here is a VBA example:

ActiveSheet.Range("C:C").NumberFormat = "@"