Can I print cell contents with cell comments at end of worksheet?

Question

How do I print the contents of a commented cell along with the cell comments at the end of a worksheet? I know how to print the comments at the end, but is it possible to add in the cell contents?

Example:

Cell E96 in my worksheet contains: "Remove storm windows"

The comment for cell E96: "6 windows plus 1 small...say .5 hours each = 3.5 hours"

When I print a worksheet with comments appended after the worksheet I get:

Cell | E96

Comment | 6 windows plus 1 small...say .5 hours each = 3.5 hours

I'd like to add the contents of the commented cell there too, like this :

(or something similar, I'm flexible)

Cell | E96 Remove storm windows

Comment | 6 windows plus 1 small...say .5 hours each = 3.5 hours

Answer 1

I took the code from chuffs link and corrected some errors.

The macro extracts the cell address, cell value, author, comment text of all commented cells and writes them to a new worksheet. This sheet can be printed separately with your normal sheet.

• open your workbook and press ALT+F11
• insert the code below into sheet1 or a new module
• close the VBA editor and go back to the sheet with your comments
• press ALT+F8 and execute the macro

---

Sub ListComments()
Application.ScreenUpdating = False

    Dim X As Long, RngName As String, newwks As Worksheet, curwks As Worksheet

    Set curwks = ActiveSheet
    If curwks.Comments.Count Then

        Set newwks = Worksheets.Add
        newwks.Range("A1").Resize(1, 4) = Array("Address", "Cell Value", "Author", "Comment")

        On Error Resume Next
        For X = 1 To curwks.Comments.Count
            With curwks.Comments.Item(X)
                newwks.Range("A1").Offset(X, 0).Resize(1, 4) = _
                    Array(.Parent.Address(False, False), .Parent.Value, .Author, .Text)
            End With
        Next
    End If

Application.ScreenUpdating = True
End Sub

Answer 2

I'm not sure if this will help you but you could try CTRL V it is supposed to copy formulas, values etc. I don't know if this will do what you want but thought you might want to try it if you are not already aware of this function.