Forces Excel to not convert numbers during moving them across sheets, or workbooks
Excel trying to be smart and converts 00000024 to 24 and 3-4 to March 4th when we move data using Value.
Mainly when we move bulk of cells using Range object.
The other option of adding single quote at start of each cell individually might not be the fastest method, so this would fix that.
' Moving data from Sheet1 to Sheet2 while forcing Excel not to convert numbers
' This will make any cell with leading zeros like 0000054 in Sheet1 to be copied as they are without removing zeros.
' Also Excel will not convert numbers that look like dates into dates, like 3-4
Sub Zero()
Sheet2.Range("A1", "AK251").FormatNumber = "@" ' Force it to be Text
Sheet2.Range("A1", "AL250").Value = Sheet1.Range("B2", "AL251").Value
End Sub
' This will make any cell with leading zeros like 0000054 in Sheet1 to be copied as they are without removing zeros.
' Also Excel will not convert numbers that look like dates into dates, like 3-4
Sub Zero()
Sheet2.Range("A1", "AK251").FormatNumber = "@" ' Force it to be Text
Sheet2.Range("A1", "AL250").Value = Sheet1.Range("B2", "AL251").Value
End Sub
Views 104
Downloads 46
CodeID
DB ID
ANmarAmdeen
610
Revisions
v3.0
Tuesday
May
23
2023