See comment locations in worksheet

Not entirely sure I follow ... pending the number of comment ranges you might get away with something like:

Sub Example()
    Dim rngComment As Range, ws As Worksheet
    Sheets.Add.Name = "Cm_@_" & Format(Now, "YYYYMMDDhhmmss")
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> ActiveSheet.Name Then
            With Cells(Rows.Count, "A").End(xlUp).Offset(1)
                On Error Resume Next
                Set rngComment = ws.Cells.SpecialCells(xlCellTypeComments)
                On Error GoTo 0
                If Not rngComment Is Nothing Then
                    .Offset(, 1).Value = rngComment.Address
                    .Value = ws.Name
                End If
            End With
        End If
    Next ws
    Application.DisplayAlerts = False
    If Cells(2, "A").Value = "" Then ActiveSheet.Delete
    Application.DisplayAlerts = True
End Sub

You can hit F5, then click Special, tick Comments and hit OK.

Now all cells with comments are highlighted. You can cycle through them by hitting the Enter key. Right-click on a cell and deal with showing / hiding / deleting the comment, or drag a visible comment to a preferred location.