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