[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2007-07-23
5
Medium Priority
?
215 Views
Last Modified: 2010-03-05
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
0
Comment
Question by:bsharath
  • 2
  • 2
5 Comments
 
LVL 16

Expert Comment

by:speshalyst
ID: 19545543
Do the comments have a specific identifier?

can u paste a sample of ur excel sheet
does it contain other"text" values ?
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 19545560
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
 
LVL 11

Author Comment

by:bsharath
ID: 19545570
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
 
LVL 11

Author Comment

by:bsharath
ID: 19545580
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
 
LVL 59

Accepted Solution

by:
Chris Bottomley earned 2000 total points
ID: 19545645
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question