Copy Comments

[abb16ott]

Hi,

Wonder if you could help, I want to be able to copy all the coments in say column E of worksheet 2 and past them into a single cell into worksheet 1 says cell E15 - how do I do that, and once I've done that I'd like to be able to copy all the comets in that cell to a differect cell (I'd also if possible be able to get the info from that cell from another spreadsheet - if that can't be done) I will be happy for the help with this

Thanks again

P.S. - I am guessing that this would have to be done using vba??

Thanks again and any help would be a juge plus for me.

[abb16ott]

Hi,

Further to this post, did find an old responce to a fellow members question which is similar to what I would like to do, what I don't understand is to adapt the vba to me needs.

If possible are you able to look at this link here http://www.excelforum.com/excel-prog...ml#post2104208

the code in question is here (hope I am not breaking the rules with copying this?) :-
Sub CopyComments()

Dim C As Long
Dim Cmmnt As Comment
Dim R As Long
Dim Rng As Range

For R = 1 To ActiveSheet.UsedRange.Rows.Count
Set Rng = ActiveSheet.Rows(R)
On Error Resume Next
Set Rng = Rng.SpecialCells(xlCellTypeComments)
If Err = 1004 Then
Err.Clear
Else
C = 10
For Each Cell In Rng
Cells(Rng.Row, C).AddComment Text:="From " & Cell.Address & vbLf & Cell.Comment.Text
Cell.Interior.ColorIndex = 6
Cells(Rng.Row, C).Interior.ColorIndex = 6
C = C + 1
Next Cell
End If
On Error GoTo 0
Next R

End Sub

Many thanks in advance