Excel: Copy and Paste into a Comment ?

General discussion on MS Excel and its spreadsheets.

Excel: Copy and Paste into a Comment ?

Postby MikeLON » Mon May 12, 2014 7:49 am

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.
Posts: 32
Joined: Tue Mar 25, 2014 6:18 am

Re: Excel: Copy and Paste into a Comment ?

Postby Champ_AN » Mon May 12, 2014 10:37 am

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.
Use the code given below:

Code: Select all
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)
  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
      'add a new comment
      'copy the formula into the comment box
      .Comment.Text Text:=.Formula
      'display the comment
      .Comment.Visible = True
    End If
  End With
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

Posts: 19
Joined: Tue Mar 25, 2014 6:34 am

Return to MS Excel- Help Forum

Who is online

Users browsing this forum: No registered users and 1 guest