Excel VBA- "paste" using RESIZE - need to paste special (scientific notation )

Question

I'm using the following code which was based on someone else's post on SO. It opens another workbook, copies a sheet and pastes it back into a main workbook. It's "pasting" these values with the resize function, so pastespecial isn't available. The problem is that there are values in the source sheet's S column that are 20+ digits (tracking numbers) and when they get pasted back to the main working book, they are truncated into scientific notation and are lost.

Set SourceFile = Workbooks.Open("C:\users\user\desktop\shipping\tempshipping.xls")

Set DestFile = ThisWorkbook


With SourceFile.Sheets("Sheet1").UsedRange

'I tried using this to set the format of the cells in the source workbook 
'to "Text" to fix the problem but I stepped through the code and actually
'checked the file when it's open and it's not doing anything

LastRow = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Range("S2:" & "S" & LastRow).NumberFormat = Text

'you can see here the "paste" method is taking the range value and resizing
'it to the source data, so pastespecial isn't available. I'm not even sure 
'doing pastespecial  would fix the problem. 

    'Now, paste to y worksheet:
    DestFile.Sheets("DestSheet").Range("A1").Resize( _
        .Rows.Count, .Columns.Count) = .Value
End With

'Close TEMP File
SourceFile.Close

extendoffice.com/documents/excel/… See there are many solutions already present you just need to search — Stupid_Intern
– Stupid_Intern, Commented Feb 21, 2016 at 17:35
That's typically an issue for dollar values. The numbers are sometimes? getting converted to 9.23454545+18 etc.. — purplefolder
– purplefolder, Commented Feb 21, 2016 at 17:35
In any case, the values should be copied as they are in the source file and they're coming in wrong. Thanks for the links, but I know how to format the cells. — purplefolder
– purplefolder, Commented Feb 21, 2016 at 17:36
Well, you don't seem to understand the basics here, the code is a bit messy, and your comments don't always make sense, but never mind... Also, you don't know how to format cells. Use .NumberFormat = "@" to format it as text... Gosh... — vacip
– vacip, Commented Feb 21, 2016 at 17:41
By the way, are the numbers in the original cell fomatted as text? Because if not, the digits after the 15th digit will be truncated anyway. It is not the scientific notation that is truncating, but Excel's limit. — vacip
– vacip, Commented Feb 21, 2016 at 17:42

user4039065 · Accepted Answer · 2016-02-21 17:41:55Z

3

The Range.NumberFormat property for text is @, not Text.

    Dim lastRow As Long, cpyrng As Range
    Dim SourceFile As Workbook, DestFile As Workbook
    Set SourceFile = Workbooks.Open("C:\users\user\desktop\shipping\tempshipping.xls")
    Set DestFile = ThisWorkbook


    With SourceFile.Sheets("Sheet1").UsedRange
        lastRow = .Cells.Find(What:="*", After:=.Cells(1), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        Set cpyrng = .Range("S2:S" & lastRow)
        cpyrng.NumberFormat = "@"   '<~~ Use @; Not Text
    End With

    'transfer values to destination worksheet
    With DestFile.Sheets("MobStub").Range("A1").Resize(cpyrng.Rows.Count, cpyrng.Columns.Count)
        .NumberFormat = "@" '<~~ set the receiving number format first
        .Value = cpyrng.Value
    End With

    'Close TEMP File
    SourceFile.Close

You were missing some . references within the With ... End With statement(s). The . in .Cells shows the parent worksheet referenced in the With ... End With.

answered Feb 21, 2016 at 17:41

user4039065

Sign up to request clarification or add additional context in comments.

2 Comments

purplefolder Over a year ago

Code came from a user here, don't shoot the novice! :) Thank you. Will take some time to sink it in. Always good to learn better formatting as well.

vacip Over a year ago

If this helped solve your problem, don't forget to mark it as answer.

Collectives™ on Stack Overflow

Excel VBA- "paste" using RESIZE - need to paste special (scientific notation )

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related