ExcelBanter - Extracting comments from all worksheets in workbook

Give this a try. It list the comment in a new sheet that it creates...

Public Sub ListComments()
Dim wks As Worksheet
Dim wksNew As Worksheet
Dim rngComments As Range
Dim rngResults As Range
Dim rng As Range

Set wksNew = Worksheets.Add
Set rngResults = wksNew.Range("A1")
On Error Resume Next

For Each wks In Worksheets
Set rngComments = Nothing
On Error Resume Next
Set rngComments = wks.Cells.SpecialCells(xlCellTypeComments)
On Error GoTo 0
If Not rngComments Is Nothing Then
For Each rng In rngComments
rngResults.Value = wks.Name
rngResults.Offset(0, 1).Value = rng.Address
rngResults.Offset(0, 2).Value = rng.Comment.Text
Set rngResults = rngResults.Offset(1, 0)
Next rng
End If
Next wks
On Error GoTo 0
End Sub

--
HTH...

Jim Thomlinson

"Barb Reinhardt" wrote:

I have about 15 worksheets in my workbook and I want to extract all of the
comments that are on each sheet as well as the location within the sheet for
those comments. Could someone give me some guidance as to where to start?

Thanks