How to Copy only Comments from Cells in Excel

Cell comments can be considered notes that provide additional information on the cell contents. Hence, converting cell comments into content can be useful for users dealing with large datasets. In this post, we will share how you can copy only comments from Cells in Excel.

How to Copy only Comments from Cells in Excel

Excel allows multiple ways through which the comments of a cell can be copied, as detailed below:

1. Copy through Paste Special
2. Copy through VB Code
3. Applying User-defined functions
4. Use Macro

1] Copy using Paste Special

The easiest way to copy comments in Excel us using the Paste Special.

• Open both the source and target Excel documents.
• Select the cells in the source document from which the comments need to be copied and right-click on it to copy the cell contents.
• On the target document, right-click on the cell where the comments will be pasted to select the Paste Special option.
• In the Paste Special dialog box, select Comments and then click OK.

2] Use VBA Code

• Press the ALT button and press F11 on the keyboard to open a Microsoft Visual Basic for Application window.
• Click Insert > Module, and copy the below-mentioned VBA into the module.

From one sheet to another

Sub CopyComments ()

Worksheets("Sheet1").Select

Range("A1").Copy

Windows("Workbook2").Activate

Worksheets("Sheet1").Select

Range("A1").PasteSpecial xlPasteComments

End Sub

In the above code, Worksheets(“Sheet1”) selects the worksheet named Sheet1 of the source file, while Range(“A1”).Copy copies the content of cell A1 along with the comments. Windows(“Workbook2”).

Activate activates the target file and Worksheets(“Sheet1”). Select, selects the worksheet in the target file where the comments are to be copied. Finally, Range(“A1”).PasteSpecial xlPasteComments pastes the copied comments only as instructed through the xlPasteComments command.

Same workbook

Sub commennt()

Dim i As Integer

For i = 1 To 10

Tabelle1.Cells(i, 2).Value = Tabelle1.Cells(i, 1).Comment.Text

Next i

End Sub

The above code will extract the comments from a cell/cells containing the content and paste them on a different cell. In this code, Dim i As Integer declares an integer variable i, while Tabelle1.Cells(i, 2).Value = Tabelle1.Cells(i, 1).Comment.Text attached to the source cells for worksheet Tabelle1 and assigns its value in cell i, 2 in the same worksheet. The loop is repeated ten times, assuming that the cell comments of ten cells are being copied.

Disclaimer: Before following the above procedure, it is recommended that a copy of the source file be kept as a backup.

• Press F5 or click on Run to execute the above VBA code.

3] Apply User-defined Formula

Besides using the Paste Special option and VBA codes to copy comments, Excel also provides the option to create user-defined functions or formulas for copying comments. The sample code for such a function is detailed below:

Function getCmnt(incell) As String

On Error Resume Next

getCmnt =incell.Comment.Text

End Function

The above function getCmnt() accepts a cell as input and checks if any comments are available for that cell. If any comments are found, they are returned as a string through the statement getCmnt =incell.Comment.Text

Once the function is defined, the exact needs to be installed or added in Excel through the below-mentioned steps:

• Open an Excel file and right-click on the worksheet to select the View Code option.
• Click on Insert -> Module.
• Paste the above code in the new module and save the worksheet to start using the function.
• Click on a cell and type =getCmnt (cell number) to copy and display the comments of that particular cell.

4] Use Macro

Macros in Excel are instructions used mainly for automating repetitive tasks. For copying comments in Excel, Macros can be used to automate the copying of cell comments by following the below-mentioned steps:

• Open the Excel file and click on Visual Basic under the Developer Tab
• Click on Insert-> Module to insert a new Module
• Paste the below-mentioned code and click Save.

Sub CopyComments()

Dim wsh As Worksheet

Dim cmt As Comment

Application.ScreenUpdating = False

Set wsh = ActiveSheet

For Each cmt In wsh.Comments

If cmt.Parent.Column = 1 Then

cmt.Parent.Offset(0, 1).Value = cmt.Text

End If

Next cmt

Application.ScreenUpdating = True

End Sub

Sub CopyComments(), declares a new function named CopyComments(), while Dim wsh As Worksheet declares a variable wsh of type worksheet and Dim cmt As Comment, declares cmt as a variable of type comment to store the cell’s comments. Application.ScreenUpdating = False disables screen updating while this function is being executed to ensure improved system performance.

Set wsh = ActiveSheet assigns the active worksheet to wsh for copying the comments from the active or selected worksheet. For Each cmt In wsh.Comments there is a loop that captures.+

If cmt.Parent.Column = 1 Then checks if the cell with the comment is =1 (column A), which, if true, would mean that the comment is in column A.

cmt.Parent.Offset(0, 1).Value = cmt.Text copies the comment text to its right-side cell with Offset(0,1) moves the cell reference to the column on the right-hand side. The loop continues till the above condition holds TRUE so that all the cell comments are copied.

• Please return to the current worksheet and click on the Macro option at the top and click on RUN under it to execute the Macro.

You can use Macro to copy comments in excel to another cell as well.