XL VBA - Paste Values that actually pastes Values ONLY?

[Dont Call me Betty]

Below code does not reliably paste Values only, different formatting gets in.
Tried different approaches, like recording a macro while doing Paste Values.
Nothing works consistently to ONLY paste Values, to match the cell formatting, font, etc.

Sub Paste()
'Tools -> References -> Microsoft Forms 2.0 Object Library
'or you will get a "Compile error: user-defined type not defined"
  Dim DataObj As New MSForms.DataObject
  Dim s As String
  DataObj.GetFromClipboard
  s = DataObj.GetText
  'Debug.Print S 'print code in the Intermediate box in the Macro editor
  Target.Value = s
End Sub

 

[NdNoviceHlp]

Perhaps...

Target.Value = s.Value

HTH. Dave

 

[Dont Call me Betty]

Perhaps...

Target.Value = s.Value

HTH. Dave

That did not make a difference

 

[Scott Huish]

Why are you using the clipboard instead of copying and pasting directly from the cells?

 

[Dont Call me Betty]

Why are you using the clipboard instead of copying and pasting directly from the cells?

data comes from different sources, like email, ERP systems etc.

 

[Scott Huish]

That worked perfectly fine for me.
I copied bold text out of an email and used your macro and it placed in Plain Text.

I wouldn't use Target though, that's used as a passed parameter on Worksheet event code

I would use Activecell

Sub Paste()
'Tools -> References -> Microsoft Forms 2.0 Object Library
'or you will get a "Compile error: user-defined type not defined"
  Dim DataObj As New MSForms.DataObject
  Dim s As String
  DataObj.GetFromClipboard
  s = DataObj.GetText
  ActiveCell.Value = s
End Sub

but if you're still having problems maybe you can add ClearFormats to the end of it.

Sub Paste()
'Tools -> References -> Microsoft Forms 2.0 Object Library
'or you will get a "Compile error: user-defined type not defined"
  Dim DataObj As New MSForms.DataObject
  Dim s As String
  DataObj.GetFromClipboard
  s = DataObj.GetText
  ActiveCell.Value = s
  ActiveCell.ClearFormats
End Sub

 

[Dont Call me Betty]

That worked perfectly fine for me.
I copied bold text out of an email and used your macro and it placed in Plain Text.

I wouldn't use Target though, that's used as a passed parameter on Worksheet event code

I would use Activecell

Sub Paste()
'Tools -> References -> Microsoft Forms 2.0 Object Library
'or you will get a "Compile error: user-defined type not defined"
  Dim DataObj As New MSForms.DataObject
  Dim s As String
  DataObj.GetFromClipboard
  s = DataObj.GetText
  ActiveCell.Value = s
End Sub

but if you're still having problems maybe you can add ClearFormats to the end of it.

Sub Paste()
'Tools -> References -> Microsoft Forms 2.0 Object Library
'or you will get a "Compile error: user-defined type not defined"
  Dim DataObj As New MSForms.DataObject
  Dim s As String
  DataObj.GetFromClipboard
  s = DataObj.GetText
  ActiveCell.Value = s
  ActiveCell.ClearFormats
End Sub

I am triggering that macro by:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Tried every combination of your suggestions, to no avail
e.g.: Copying from another XL file where the cell background is yellow into a cell with a background in a different color, in a workbook containing the macro, and cell becomes yellow, instead of keeping own color.