Does anyone know a quick way to print all the comments in an excel worksheet
to either a row or a separate sheet?

zulfer7

FilePage setup...SheetComments= at end of sheet

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England

HIS
www.nickhodge.co.uk

"zulfer7" wrote in message
...
Does anyone know a quick way to print all the comments in an excel
worksheet
to either a row or a separate sheet?

This macro will put all comments from the active sheet onto a new sheet ready
for printing.

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


Gord Dibben MS Excel MVP

On Thu, 19 Oct 2006 13:53:02 -0700, zulfer7
wrote:

Does anyone know a quick way to print all the comments in an excel worksheet
to either a row or a separate sheet?