'Copying a string, which is all numerical digits

Am going crazy trying to just copy and paste in Excel.

I have a worksheet that I am exporting, from AutoCAD if it matters, and then trying to copy and paste a section of it onto another sheet. One of the columns has a four-digit number, starting with 0, that I would like to keep as a string. (I think AutoCAD is exporting it as a string, and expects it back as a string for importing, which explains why I do not want any workaround where the string TYPE is lost)

When I do the copy/paste "manually" with CTRL+C and CTRL+V, the columns paste fine (e.g., "0101" pastes as "0101" although the top left corner of the cells are greened with a message that says "the number in the cell is formatted as text". I'm thinking: no, it's a string, just leave it alone, please! And it does leave it alone, there.

However, my VBA script to do the same seems to lose the TYPE of the value when pasting (e.g., "0101" becomes 101, and there are no green corners with comments). I have verified this with the manual TYPE function under the Formulas bar.

Here is my script:

ThisWorkbook.Worksheets("Sheet1").Range("B" & numTemplateHeaderRows + 1 & ":Y" & numImportRows + 1).Value = _
    wsImport.Range("B" & numTemplateHeaderRows + 1 & ":Y" & numImportRows + 1).Value

I've even tried to Dim an array, put the values in there, and then iterate through the problematic column with Cstr. Still, when I set the values on the receiving sheet, those Strings become numbers.

Does anyone know what might be causing Excel to do this conversion? Can I turn it off, please?

Solution 1:^[1]

If you want to copy-paste a cell or range, use PasteSpecial:

Sub copyRange(wsImport As Worksheet, numTemplateHeaderRows As Integer, numImportRows As Integer)

    Dim org As Range, dest As Range
    Set org = wsImport.Range("B" & numTemplateHeaderRows + 1 & ":Y" & numImportRows + 1)
    Set dest = ThisWorkbook.Worksheets("Sheet1").Range("B" & numTemplateHeaderRows + 1 & ":Y" & numImportRows + 1)
    org.Copy
    dest.PasteSpecial xlPasteAll

End Sub

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1

'Copying a string, which is all numerical digits

Solution 1:[1]

Sources

Related Questions

Solution 1:^[1]