Including number format paste special in the Macro

[Dario88m]

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

[#2]

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