Excel: Copy and Paste into a Comment ?

[ElisCom]

Hi everyone,



I searched the Forum but I didn't find anything about it.

How is it possible to copy data and paste them into a comment in a specific cell?

Once in a week I have to fill 10x7=70 comments in cells of an Excel file with a lot of data that I get from an other excel file.

This work takes a lot of time entering manually all data!! I was wondering if it is possible to simplify this work.

Thank you in advance for your help.

Andrea

[macropod]

Yes, it is possible to use a macro to add content to comments. For example, the following macro adds the formulae to the comments for each selected cell, or even the whole worksheet, and displays the comments in an appropriately-sized box.

Sub AddFormulasToComments()
Application.ScreenUpdating = False
Dim CommentRange As Range, TargetCell As Range
'skip over errors caused by trying to delete comments in cells with no comments
On Error Resume Next
'If the whole worksheet is selected, limit action to the used range.
If Selection.Address = Cells.Address Then
  Set CommentRange = Range(ActiveSheet.UsedRange.Address)
Else
  Set CommentRange = Range(Selection.Address)
End If
'If the cell contains a formula, turn it into a comment.
For Each TargetCell In CommentRange
  With TargetCell
    'check whether the cell has a formula
    If Left(.Formula, 1) = "=" Then
      'delete any existing comment
      .Comment.Delete
      'add a new comment
      .AddComment
      'copy the formula into the comment box
      .Comment.Text Text:=.Formula
      'display the comment
      .Comment.Visible = True
    End If
  End With
Next
MsgBox "  To print the comments, choose" & vbCrLf & " File|Page Setup|Sheet|Comments," & vbCrLf & "then choose the required print option.", vbOKOnly
Application.ScreenUpdating = True
End Sub

[ElisCom]

Thank you,

I will try to use it today!

Cheers

Andrea