Macro for excel to get all the comments to a new page

Hi,

I want a macro which can search the page and get all the comments found in a sheet with the full line content to a new sheet.

regards
Sharath
LVL 11
bsharathAsked:
Who is Participating?
 
Chris BottomleySoftware Quality Lead EngineerCommented:
BSHARATH

For example try the following:

Sub ee_22713451_2()
Dim new_sheet As Worksheet
Dim sh As Worksheet
'Dim source_sheet As Worksheet
Dim new_sheet_name As String
Dim user_response As Integer
Dim cmt As Comment
Dim row_count As Long
Dim row_check As Long
row_count = 0
new_sheet_name = "Processed Data"

'Set source_sheet = ActiveSheet
For Each new_sheet In Worksheets
    If new_sheet.Name = new_sheet_name Then
        user_response = MsgBox("Target Sheet name: """ & new_sheet_name & """ Exists already.  Delete it and continue?", vbYesNo, "Delete and Continue?")
        If user_response = vbYes Then
            Application.DisplayAlerts = False
            new_sheet.Delete
            Application.DisplayAlerts = True
        Else
            GoTo exiat1
        End If
    End If
Next
    Set new_sheet = ThisWorkbook.Worksheets.Add
    new_sheet.Name = new_sheet_name
    For Each sh In ThisWorkbook.Worksheets
        row_check = 0
        For Each cmt In sh.Comments
            If row_check <> cmt.Parent.Row Then
                row_check = cmt.Parent.Row
                row_count = row_count + 1
                cmt.Parent.EntireRow.Copy new_sheet.Cells(row_count, 1)
            End If
        Next
    Next
exiat1:
End Sub

Regards
Chris
0
 
speshalystCommented:
Do the comments have a specific identifier?

can u paste a sample of ur excel sheet
does it contain other"text" values ?
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
Full line content?

DO you want all the comment, (or all cell data) for a given row or d you mean all the comment text for a given cell comment

Chris
0
 
bsharathAuthor Commented:
No comments are of different data i it.

I have a excel which has 1000+ rows and 20 colums and every row has comments.Comments many vary from colum A,B,C etc.

0
 
bsharathAuthor Commented:
Ex:

If i have a comment in Colum K in line 5.I need the full line 5 with the comment in a new sheet
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.