Extract Cell Comments and Paste as text in a cell

[Goaliemenace]

Is there an easier function/process to copy cell comments and paste directly
into a cell as text, without having to edit the comment first and select the
required text manually? Copy and paste comments does not work, it merely
keeps the same character when pasted into a new cell.

 

[Gord Dibben]

Code by Dave Peterson........posted on Debra Dalgleish's site.

http://www.contextures.on.ca/xlcomments03.html#CopyAdjacent

Sub ShowCommentsNextCell()
'based on code posted by Dave Peterson 2003-05-16
Application.ScreenUpdating = False

Dim commrange As Range
Dim mycell As Range
Dim curwks As Worksheet

Set curwks = ActiveSheet

On Error Resume Next
Set commrange = curwks.Cells _
.SpecialCells(xlCellTypeComments)
On Error GoTo 0

If commrange Is Nothing Then
MsgBox "no comments found"
Exit Sub
End If

For Each mycell In commrange
If mycell.Offset(0, 1).Value = "" Then
mycell.Offset(0, 1).Value = mycell.Comment.Text
End If
Next mycell

Application.ScreenUpdating = True

End Sub


Gord Dibben MS Excel MVP

 

[Gary''s Student]

Select the cells in question and run nthis small macro:

Sub comment_setter()
Dim r As Range, rr As Range
On Error Resume Next
Set r = Selection.SpecialCells(xlCellTypeComments)
If r Is Nothing Then
MsgBox ("no comments found")
Exit Sub
End If

For Each rr In r
rr.Value = rr.comment.Text
Next
End Sub

 

[Goaliemenace]

Perfect - thank you

Select the cells in question and run nthis small macro:

Sub comment_setter()
Dim r As Range, rr As Range
On Error Resume Next
Set r = Selection.SpecialCells(xlCellTypeComments)
If r Is Nothing Then
MsgBox ("no comments found")
Exit Sub
End If

For Each rr In r
rr.Value = rr.comment.Text
Next
End Sub