Hi all
I have a sheet called data in a workbook, on this sheet are over 3500 lines of data. many of the cells have comments in. What I need to do is extract the comments to a separate sheet (called data_Comments) in workbook. Now I have found this code:
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
Now this does work but I need more info extracted.
My data sheet has the column headings (in Col): Date (col A), Opponents (col B), V (col C). I need these to be the first three columns on the data_Comments sheet. Then after these the New Col D to have the heading Address, Col E Value, Col F = Comment Text (as the macro above).
Can anyone advise how to achieve this?
Thanks in advance.
Bookmarks