Macro to get comments out of a spreadsheet
 

I would like to create a macro to get the commnets out of each cell and
then add and display in another worksheet


--
shanelaura
------------------------------------------------------------------------
shanelaura's Profile: http://www.excelforum.com/member.php...o&userid=33898
View this thread: http://www.excelforum.com/showthread...hreadid=536733

Macro to get comments out of a spreadsheet
 

Record as a macro the following steps:
Select all cells
Copy
Select new sheet
Pastespecial Comments

Regards,
Stefi


€žshanelaura€� ezt Ã*rta:


I would like to create a macro to get the commnets out of each cell and
then add and display in another worksheet


--
shanelaura
------------------------------------------------------------------------
shanelaura's Profile: http://www.excelforum.com/member.php...o&userid=33898
View this thread: http://www.excelforum.com/showthread...hreadid=536733

Macro to get comments out of a spreadsheet
 

Thank you for the reply
Not quite the result I'm looking for
I would like to see the comments added to a blank spread not in commen
form but in text form if that makes sens

--
shanelaur
-----------------------------------------------------------------------
shanelaura's Profile: http://www.excelforum.com/member.php...fo&userid=3389
View this thread: http://www.excelforum.com/showthread.php?threadid=53673

Macro to get comments out of a spreadsheet
 

shanelaura,

Private Sub CommandButton1_Click()
Dim rng As Range
Dim i As Long

For Each rng In Selection
If Not rng.Comment Is Nothing Then
ThisWorkbook.Worksheets(2).Range("A1").Offset(i, 0).Value =
rng.Comment.Text
i = i + 1
End If
Next

End Sub

NickHK

"shanelaura" wrote
in message ...

Thank you for the reply
Not quite the result I'm looking for
I would like to see the comments added to a blank spread not in comment
form but in text form if that makes sense


--
shanelaura
------------------------------------------------------------------------
shanelaura's Profile:
http://www.excelforum.com/member.php...o&userid=33898
View this thread: http://www.excelforum.com/showthread...hreadid=536733

Macro to get comments out of a spreadsheet
 

Hi,

Here is some code to get you started.

Sub TOC_Comments()

Dim rngCmt As Range
Dim rngCmts As Range
Dim rngOutput As Range

Set rngCmts = ActiveSheet.Cells.SpecialCells(xlCellTypeComments)
Set rngOutput = Worksheets.Add.Range("A1")

For Each rngCmt In rngCmts.Cells
rngOutput = "'" & rngCmt.Parent.Name & "'!" & rngCmt.Address
rngOutput.Offset(0, 1) = rngCmt.Comment.Text
Set rngOutput = rngOutput.Offset(1, 0)
Next

End Sub

Cheers
Andy

shanelaura wrote:
Thank you for the reply
Not quite the result I'm looking for
I would like to see the comments added to a blank spread not in comment
form but in text form if that makes sense



--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

Macro to get comments out of a spreadsheet
 

Sorry, I misunderstood your request. This UDF will display comments in source
sheet (Sheet1) as cell contents in destination sheet (Sheet2):

Function CommText(sourcesheet, cellrow, cellcol)
If Worksheets(sourcesheet).Cells(cellrow, cellcol).Comment Is Nothing Then
CommText = ""
Else
CommText = Worksheets(sourcesheet).Cells(cellrow,
cellcol).Comment.Text
End If
End Function

Enter in A1 in Sheet2
=CommText("Sheet1",ROW(),COLUMN())

and fill it right and down as required!

Regards,
Stefi


€žshanelaura€� ezt Ã*rta:


Thank you for the reply
Not quite the result I'm looking for
I would like to see the comments added to a blank spread not in comment
form but in text form if that makes sense


--
shanelaura
------------------------------------------------------------------------
shanelaura's Profile: http://www.excelforum.com/member.php...o&userid=33898
View this thread: http://www.excelforum.com/showthread...hreadid=536733

Macro to get comments out of a spreadsheet
 

Here is something else you might be interested in:

http://www.cpearson.com/excel/excelM.htm#PrintComments

A Chip Pearson's site.

--
Regards,
Tom Ogilvy



"shanelaura" wrote:


Thank you for the reply
Not quite the result I'm looking for
I would like to see the comments added to a blank spread not in comment
form but in text form if that makes sense


--
shanelaura
------------------------------------------------------------------------
shanelaura's Profile: http://www.excelforum.com/member.php...o&userid=33898
View this thread: http://www.excelforum.com/showthread...hreadid=536733