List all comments from sheet on separate sheet in Excel

Contact me regarding customizing this template for your needs.

Excel one-on-one on-line training available. Email me to arrange.

I am able to provide online help on your computer at a reasonable rate.

Check out my next one-hour Excel Webinar

I use a Blue condensor Microphone to record my videos, here is the link

Check out Crowdcast for creating your webinars

If you need to buy Office 2019 follow

I use Tube Buddy to help promote my videos
Check them out

Follow me on Facebook

TWEET THIS VIDEO

Follow me on twitter
easyexcelanswers

IG @barbhendersonconsulting

You can help and generate a translation to you own language

*this description may contain affiliate links. When you click them, I may receive a small commission at no extra cost to you. I only recommend products and services that I’ve used or have experience with.

Follow me on Facebook

Sub listComments()
Dim liComment As Comment
Dim i As Integer
Dim ws As Worksheet
Dim ComSh As Worksheet
Set ComSh = ActiveSheet
If ActiveSheet.Comments.Count = 0 Then Exit Sub

For Each ws In Worksheets
If ws.Name = “Comments” Then i = 1
Next ws

If i = 0 Then
Set ws = Worksheets.Add(After:=ActiveSheet)
ws.Name = “Comments”
Else: Set ws = Worksheets(“Comments”)
End If

For Each liComment In ComSh.Comments
ws.Range(“A1”).Value = “Comment In”
ws.Range(“B1”).Value = “Comment By”
ws.Range(“C1”).Value = “Comment”
With ws.Range(“A1:C1”)
.Font.Bold = True
.Interior.Color = RGB(189, 215, 238)
.Columns.ColumnWidth = 20
End With
If ws.Range(“A2”) = “” Then
ws.Range(“A2”).Value = liComment.Parent.Address
ws.Range(“B2”).Value = Left(liComment.Text, InStr(1, liComment.Text, “:”) – 1)
ws.Range(“C2”).Value = Right(liComment.Text, Len(liComment.Text) – InStr(1, liComment.Text, “:”))
Else
ws.Range(“A1”).End(xlDown).Offset(1, 0) = liComment.Parent.Address
ws.Range(“B1”).End(xlDown).Offset(1, 0) = Left(liComment.Text, InStr(1, liComment.Text, “:”) – 1)
ws.Range(“C1”).End(xlDown).Offset(1, 0) = Right(liComment.Text, Len(liComment.Text) – InStr(1, liComment.Text, “:”))
End If
Next liComment
End Sub

Watch more new videos about Excel Office | Synthesized by Mindovermetal English