Hi all!
I have an existing workbook with approx 30 worksheets and over 50 threaded comments (ie not the legacy Notes from previous version of excel) scattered throughout the various 30 odd worksheets. I would like to extract all the threaded comments from all worksheet into a separate worksheet for reference.
I managed to get the following VBA code off the contextures.com site which works perfectly fine for extracting all threaded comments from 1 active sheets
---------------------------------------------------------------------------------------
Sub ListCommentsRepliesThreaded()
Application.ScreenUpdating = False
Dim myCmt As CommentThreaded
Dim myRp As CommentThreaded
Dim curwks As Worksheet
Dim newwks As Worksheet
Dim myList As ListObject
Dim i As Long
Dim iR As Long
Dim iRCol As Long
Dim ListCols As Long
Dim cmtCount As Long
Set curwks = ActiveSheet
cmtCount = curwks.CommentsThreaded.Count
If cmtCount = 0 Then
MsgBox "No threaded comments found"
Exit Sub
End If
Set newwks = Worksheets.Add
newwks.Range("A1:F1").Value = _
Array("Number", "Cell", "Author", _
"Date", "Replies", "Text")
i = 1
For Each myCmt In curwks.CommentsThreaded
With newwks
i = i + 1
On Error Resume Next
.Cells(i, 1).Value = i - 1
.Cells(i, 2).Value = myCmt.Parent.Address
.Cells(i, 3).Value = myCmt.Author.Name
.Cells(i, 4).Value = myCmt.Date
.Cells(i, 5).Value = myCmt.Replies.Count
.Cells(i, 6).Value = myCmt.Text
If myCmt.Replies.Count > 1 Then
iR = 1
iRCol = 7
For iR = 1 To myCmt.Replies.Count
.Cells(1, iRCol).Value = "Reply " & iR
.Cells(i, iRCol).Value _
= myCmt.Replies(iR).Author.Name _
& vbCrLf _
& myCmt.Replies(iR).Date _
& vbCrLf _
& myCmt.Replies(iR).Text
iRCol = iRCol + 1
Next iR
End If
End With
Next myCmt
With newwks
.ListObjects.Add(xlSrcRange, _
.Cells(1, 1) _
.CurrentRegion, , xlYes) _
.Name = ""
End With
Set myList = newwks.ListObjects(1)
myList.TableStyle = "TableStyleLight8"
ListCols = myList.DataBodyRange _
.Columns.Count
With myList.DataBodyRange
.Cells.VerticalAlignment = xlTop
.Columns.EntireColumn.ColumnWidth = 30
.Cells.WrapText = True
.Columns.EntireColumn.AutoFit
.Rows.EntireRow.AutoFit
End With
Application.ScreenUpdating = True
End Sub
---------------------------------------------------------------------------------------
Any excel VBA gurus able to assist and let me know if there is any way to modify the above code to enable it to extract all threaded comments from all worksheets in the workbook?
Thank you so much in advance. Everything else I found through google is only for the legacy Note features and does not work for the new "threaded comments" features.
Bookmarks