VBA code to list all comments to a new worksheet in workbook!!!

Comment in excel is a way of annotate calculation logic of certain measure or KPI. It will be very useful to transfer data manipulation logic user to user. As a results, there is extension use of comments take place in a workbook. It will be very difficult to check each and every comments if comments scattered in different ranges in the workbook. Thus, it will be a quite problematic task to gather information of all the comments on the singe place.

In this situation a simple excel macro will be very helpful to gather all comments in a list. Using excel macro you can list all the comments within a minute.

Here is the code which will be very useful to list down all the comments from the workbook:

Sub List_All_Comments()
' Author: Dreams24
' Written for VBA Tricks and tips blog
' https://www.vbatricksntips.com

'Declare Variable
Dim nSht As Worksheet
Dim Sht As Worksheet
Dim Rng As Range
Dim i As Integer
Dim cell As Variant

Application.ScreenUpdating = False
Application.DisplayAlerts = False

'Add new sheet in the workbook with "Comment List" name
Set nSht = Sheets.Add
nSht.Name = "Comment List"
nSht.Range("A1") = "Sheet Name"
nSht.Range("B1") = "Cell Address"
nSht.Range("C1") = "Comments"
nSht.Range("D1") = "Cell value"

i = 2

'Loop through all sheets in a workbook
For Each Sht In ActiveWorkbook.Worksheets
    On Error Resume Next
    
    'Set Rng for comment ranges
    Set Rng = Sht.Cells.SpecialCells(xlCellTypeComments)
    
    If Not Rng Is Nothing Then

    'Loop through all comment ranges
        For Each cell In Rng
            nSht.Range("A" & i) = Sht.Name
            nSht.Range("B" & i) = cell.Address
            nSht.Range("C" & i) = cell.Comment.Text
            nSht.Range("C" & i).WrapText = False
            nSht.Range("D" & i) = cell.Value
            i = i + 1
        Next cell
    End If
    
    Set Rng = Nothing
Next Sht

Application.ScreenUpdating = True
Application.DisplayAlerts = True

MsgBox "All comments are listed in Comment List Sheet!!!", vbInformation

End Sub