Forum Discussion

Dario88m's avatar
Dario88m
Copper Contributor
Jan 13, 2023

Including number format paste special in the Macro

Hi! Is it possible to paste values formatted as number? Somehow after running the macro certain values in the sheets are "number stored as text", resulting in #N/A for lookups in the following steps/worbooks.

Example:

Set b = Worksheets("Batch").Range("a2:a9999").Find(Worksheets("1").Range("a2"))  ' Finds cell with batch number - this value is 8 digits yymmdd01,02 and so on


If b Is Nothing Then ' Selects first empty line
    ans = MsgBox("This action will set the batch as completed and erase the information in the template. Do you want to continue?", 4, "Batch completion confirmation")
    
    If ans = 6 Then
        Worksheets("1").Activate
        Range("a2:ac2").Select  ' Selects all the roll lines at once
        Selection.Copy
        Worksheets("Batch").Activate
        [A9999].End(xlUp)(2, 1).Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
            Application.CutCopyMode = False
        

        
        Worksheets("1").Activate
        Range("a4:d6").Select
        Selection.Copy
        Worksheets("Lane").Activate
        [A9999].End(xlUp)(2, 1).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
            Application.CutCopyMode = False

 

Any ideas? I tried already this xlPasteValuesAndNumberFormats without success.

Thanks

  • XXplore's avatar
    XXplore
    Brass Contributor
    Use Data -> Text to Columns can solve this issue.

    You can find more infomation in Excel Help: Move pointer to a cell with green mark -> An exclamation menu pop out -> click "Help on this error"

    Action could be recorded:
    Selection.TextToColumns Destination:=Range("I1"), _
    DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, _
    ConsecutiveDelimiter:=False, _
    Tab:=True, _
    Semicolon:=False, _
    Comma:=False, _
    Space:=False, _
    Other:=False, _
    FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True

Share