Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Converting comments to data
Hi There,
I have a basic spreadsheet set up and in one particular column, each cell has a comment attached. Is there some way that I create a new column and copy each of those comments into the cells so that they become part of the data in the spreadsheet? I don't want to cut and paste each individually as there are hundreds of cells but do them on one go. Is this possible? Thanks in advance for your help. -- Cheers GLR |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Converting comments to data
Here is a very nice sample workbook: http://www.contextures.on.ca/CommentsNumberPrint.zip Tim "glarosa" wrote: Hi There, I have a basic spreadsheet set up and in one particular column, each cell has a comment attached. Is there some way that I create a new column and copy each of those comments into the cells so that they become part of the data in the spreadsheet? I don't want to cut and paste each individually as there are hundreds of cells but do them on one go. Is this possible? Thanks in advance for your help. -- Cheers GLR |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Converting comments to data
Hi,
Right click your sheet tab, view code and paste this in. Currently it uses Column A so change to suit. It will copy comments into the cell to the right of the column you use and then delete th original comment. Sub marine() Application.ScreenUpdating = False Set myrange = Range("A1:a100") For Each c In myrange On Error Resume Next c.Offset(, 1).Value = c.Comment.Text c.ClearComments Next Application.ScreenUpdating = True End Sub Mike "glarosa" wrote: Hi There, I have a basic spreadsheet set up and in one particular column, each cell has a comment attached. Is there some way that I create a new column and copy each of those comments into the cells so that they become part of the data in the spreadsheet? I don't want to cut and paste each individually as there are hundreds of cells but do them on one go. Is this possible? Thanks in advance for your help. -- Cheers GLR |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Converting comments to data
Thanks Mike. I did as you said but I can't seem to get back to the worksheet
and activate the code. Sorry but I'm a very basic user. Can you explain further? Many Thanks -- Cheers GLR "Mike H" wrote: Hi, Right click your sheet tab, view code and paste this in. Currently it uses Column A so change to suit. It will copy comments into the cell to the right of the column you use and then delete th original comment. Sub marine() Application.ScreenUpdating = False Set myrange = Range("A1:a100") For Each c In myrange On Error Resume Next c.Offset(, 1).Value = c.Comment.Text c.ClearComments Next Application.ScreenUpdating = True End Sub Mike "glarosa" wrote: Hi There, I have a basic spreadsheet set up and in one particular column, each cell has a comment attached. Is there some way that I create a new column and copy each of those comments into the cells so that they become part of the data in the spreadsheet? I don't want to cut and paste each individually as there are hundreds of cells but do them on one go. Is this possible? Thanks in advance for your help. -- Cheers GLR |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Converting comments to data
Thanks for your help Tim, but although I could open the spreadsheet, it would
not activate the macro because of the security level. Hence I could not gte the thing to work. -- Cheers GLR "Tim" wrote: Here is a very nice sample workbook: http://www.contextures.on.ca/CommentsNumberPrint.zip Tim "glarosa" wrote: Hi There, I have a basic spreadsheet set up and in one particular column, each cell has a comment attached. Is there some way that I create a new column and copy each of those comments into the cells so that they become part of the data in the spreadsheet? I don't want to cut and paste each individually as there are hundreds of cells but do them on one go. Is this possible? Thanks in advance for your help. -- Cheers GLR |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Converting comments to data
Hi,
First change your security level-ToolsOptionsSecurityMacro SecurityChoose the €śLow€ť option. Then Copy this code: Sub showcomments() 'posted by Dave Peterson 2003-05-16 Application.ScreenUpdating = False Dim commrange As Range Dim cmt As Comment Dim curwks As Worksheet Dim newwks As Worksheet Dim i As Long Set curwks = ActiveSheet On Error Resume Next Set commrange = curwks.Cells _ .SpecialCells(xlCellTypeComments) On Error GoTo 0 If commrange Is Nothing Then MsgBox "no comments found" Exit Sub End If Set newwks = Worksheets.Add newwks.Range("A1:D1").Value = _ Array("Number", "Name", "Value", "Comment") i = 1 For Each cmt In curwks.Comments With newwks i = i + 1 On Error Resume Next .Cells(i, 1).Value = i - 1 .Cells(i, 2).Value = cmt.Parent.Name.Name .Cells(i, 3).Value = cmt.Parent.Value .Cells(i, 4).Value = cmt.Parent.Address .Cells(i, 5).Value = Replace(cmt.Text, Chr(10), " ") End With Next cmt newwks.Cells.WrapText = False newwks.Columns.AutoFit Application.ScreenUpdating = True End Sub Go to your workbookAlt+F11InsertModule and Paste the copied Code. Close the VBA EditorSelect your worksheet with the commentsAlt+F8 and Run the macro €śshowcomments€ť. After running the macro it will list all comments from your worksheet regardless in which column are they on a new Worksheet. If you have comments in more the one column and you insist to list only the comments from that particular column then use the Mikes suggestion. Hope this helps. Tim "glarosa" wrote: Thanks Mike. I did as you said but I can't seem to get back to the worksheet and activate the code. Sorry but I'm a very basic user. Can you explain further? Many Thanks -- Cheers GLR |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reformattign Data/Converting-Combinign Vertical Data to Horizontal | Excel Worksheet Functions | |||
HOW DO I COPY ALL DATA (INC COMMENTS)FROM 1 WORKBOOK TO ANOTHER | Excel Worksheet Functions | |||
in excel useing comments how do you add clip art to comments? | New Users to Excel | |||
Gathering Comments from Survey Data | Excel Worksheet Functions | |||
Converting Comments | Excel Discussion (Misc queries) |