The Name should be pulling the Range Name, if one was defined. There is
apparently no Name defined for the cells with Comments in them, so that
field is coming up empty. You could probably skip that column to save space.
As for the modification, is this related to where the CellComments are being
found, or is this completely non-related to this code?

Mike F
"DPeter2101" wrote in
message ...

I am having problems adjusting a VB script I got from a link off this
forum. It finds any cell in my work book that contains comments and
adds them to a new work sheet. It pulls
Sheet, Address, Name, Value and Comment. Sheet is the name of the work
sheet. Address is the cell ID. Name is not working for me (Not sure
what this should be pulling). Comments pull comments. What I need to
add to this is:

In my work book A5 to A70 are employee names. B5-70 to AF5-70 is where
there will be notes. If there is a note in B5 I would like to add the
name in A5 to the new work sheet in F1.

Any help would be appreciated!

Sub ShowCommentsAllSheets()
'modified from code
' by Dave Peterson
Application.ScreenUpdating = False

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

Set newwks = Worksheets.Add

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

For Each ws In ActiveWorkbook.Worksheets
On Error Resume Next
Set commrange = ws.Cells.SpecialCells(xlCellTypeComments)
On Error GoTo 0

If commrange Is Nothing Then
'do nothing
Else

i = newwks.Cells(Rows.Count, 1).End(xlUp).Row

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

End With
Next mycell
End If
Set commrange = Nothing
Next ws


--
DPeter2101
------------------------------------------------------------------------
DPeter2101's Profile:
http://www.excelforum.com/member.php...o&userid=29726
View this thread: http://www.excelforum.com/showthread...hreadid=494406