Pasting Into a Comment

Thanks Willy. Nice!

@Harold
Excelent idea but the macro can be simplfied a bit:

Sub PasteToComment()
Dim strData As String
' copy some range values
Sheet1.Range("C1:C31").Copy
' get the clipboard data
Dim O As New DataObject
O.GetFromClipBoard
strData = O.GetText
With ActiveCell
If .Comment Is Nothing Then .AddComment
.Comment.Text Text:=strData
End With
End Sub

You can also remove the "copy marching ants" by adding
Application.CutCopyMode = False

Harold

Sub PasteToComment()

Thank you! Thank you! Thank you!

Worked perfectly once I leaarned how to set a reference to Microsoft.Forms

Tim
Here is a macro that do what you want.
Make sure to set a reference to Microsoft.Forms

Sub PasteToComment()
Dim rngData As Range
Dim strData As String
Dim sActiveCell
' copy some range values
Set rngData = Sheet1.Range("C1:C31")
rngData.Copy
' get the clipboard data
' magic code for is for early binding to MSForms.DataObject
With CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
.GetFromClipBoard
strData = .GetText
End With
sActiveCell = ActiveCell.Address
With ActiveCell
If Not .Comment Is Nothing Then
Range(sActiveCell).Comment.Text Text:=strData
Else
Range(sActiveCell).AddComment
Range(sActiveCell).Comment.Text Text:=strData
End If
End With
End Sub

Tim, try this macro

Sub copy2Comment()
Dim cmt As Comment, rng As Range, _
rng2Copy As Range, c As Range
Dim txt As String, str As String, Addr As String

Set rng = Selection.Areas(1)
Set rng2Copy = Selection.Areas(2)
If Selection.Areas.Count <> 2 Then
MsgBox "Select two Areas!", vbCritical
Exit Sub
End If
Addr = rng.Address
'pgc01 - Mr Excel 5th Aug 2006
If Range(Addr).Comment Is Nothing Then
Range(Addr).AddComment
Else
txt = Range(Addr).Comment.Text
End If
For Each c In rng2Copy
str = str & c & Chr(10)
Next

Range(Addr).Comment.Shape.Select
Range(Addr).Comment.Text Text:=txt & str
Range(Addr).Comment.Shape.TextFrame.AutoSize = True
End Sub

You must select two ranges, the comment range and the range to copy in that order. The comment range can be part of the data set; and so will be selectred twice. Note, it does actally copy anythng ;- )

So how would this be done with a macro. Paste a range (C1:C31) of textI in to the comments of the active cell.

@rajendra: I don't think you can paste into an Excel comment "with format." If you pick Review > Edit Comment, then right-click within the comment, you will see only one Paste option (which is the same as Ctrl+V). I don't think you can even develop a macro to paste into an Excel comment "with format," but it's an interesting challenge.

I have table in MS outlook email body.
This has to be pasted in Excel in comment as it is with format
Request your help

I hadn't really used the handy Office Clipboard. Pretty slick. And it was right there all the time.

You could use the CopyValues macro I posted 2018-08-05; see http://excelribbon.tips.net/T013558_Pasting_Numeric_Values_in_Other_Programs.html#comment-form-hd