Extract Cell Comments and Paste as text in a cell
 

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.

Extract Cell Comments and Paste as text in a cell
 

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

http://www.contextures.on.ca/xlcomme...l#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

On Fri, 16 Oct 2009 14:03:01 -0700, Goaliemenace
wrote:

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.

Extract Cell Comments and Paste as text in a cell
 

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
--
Gary''s Student - gsnu200907


"Goaliemenace" wrote:

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.

Extract Cell Comments and Paste as text in a cell
 

Perfect - thank you

"Gary''s Student" wrote:

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
--
Gary''s Student - gsnu200907


"Goaliemenace" wrote:

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.