Excel comment

Hi,

This link should help

http://www.contextures.com/xlcomment...ml#CopyToSheet

VBA Noob

It's very easy and quick

Press Alt + F11
Select Insert > Module

Select the below code and paste in. Close Editor

Then press

ALT + F8 and select Showcomments macro to run

Sub showcomments()
'posted by Dave Peterson 2003-05-16 
    Application.ScreenUpdating = False

    Dim commrange As Range
    Dim mycell As Range
    Dim curwks As Worksheet
    Dim newwks As Worksheet
    Dim i As Long

    Set curwks = ActiveSheet

    On Error Resume Next
    Set commrange = curwks.Cells _
        .SpecialCells(xlCellTypeComments)
    On Error GoTo 0

    If commrange Is Nothing Then
       MsgBox "no comments found"
       Exit Sub
    End If

    Set newwks = Worksheets.Add

     newwks.Range("A1:D1").Value = _
         Array("Address", "Name", "Value", "Comment")

    i = 1
    For Each mycell In commrange
       With newwks
         i = i + 1
         On Error Resume Next
         .Cells(i, 1).Value = mycell.Address
         .Cells(i, 2).Value = mycell.Name.Name
         .Cells(i, 3).Value = mycell.Value
         .Cells(i, 4).Value = mycell.Comment.Text
       End With
    Next mycell

    Application.ScreenUpdating = True

End Sub

VBA Noob

Try

Sub showcomments()
'posted by Dave Peterson 2003-05-16
    Application.ScreenUpdating = False

    Dim commrange As Range
    Dim mycell As Range
    Dim curwks As Worksheet
    Dim newwks As Worksheet
    Dim i As Long

    Set curwks = ActiveSheet

    On Error Resume Next
    Set commrange = curwks.Cells _
        .SpecialCells(xlCellTypeComments)
    On Error GoTo 0

    If commrange Is Nothing Then
       MsgBox "no comments found"
       Exit Sub
    End If

    Set newwks = Worksheets.Add

     newwks.Range("A1:D1").Value = _
         Array("Address", "Name", "Value", "Comment")

    i = 1
    For Each mycell In commrange
       With newwks
         i = i + 1
         On Error Resume Next
         .Cells(i, 1).Value = mycell.Address
         .Cells(i, 2).Value = mycell.Name.Name
         .Cells(i, 3).Value = mycell.Value
         .Cells(i, 4).Value = mycell.Comment.Text
       End With
    
    Next mycell
    Cells.Select
        With Selection
        .HorizontalAlignment = xlCenter ' or  xlRight or  xlLeft
        End With
     Cells.EntireColumn.AutoFit
     Range("A1").Select
    Application.ScreenUpdating = True

End Sub

VBA Noob