Forum Discussion
Dario88m
Jan 13, 2023Copper Contributor
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
- XXploreBrass ContributorUse 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