How to Copy Cell Comments to Another Cell via VBA

[jay333]

I am seeking a way to copy a cells contents to another cell (including comments) to another cell. I am trying to format it something like:

sheet1.cells(2,3) = sheet2.cells(5,6).

the above does not move comments that exist in the sheet2 cell to the sheet 1 cell.

Was trying something like sheet1.cells(2,3) = sheet2.cells(5.6).comments.text. However, this does not work.

Any suggestions?

 

[diddi]

you could use pastespecial to dump the comments...
copy the contents then:

    Selection.PasteSpecial Paste:=xlPasteComments, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False

 

[GTO]

...answered

 

[AlphaFrog]

Another method...

[color=darkblue]With[/color] Sheet1.Cells(2, 3)
    .ClearComments [color=green]'Clear comment if already exists[/color]
    .AddComment Sheet2.Cells(5, 6).Comment.Text
[color=darkblue]End[/color] [color=darkblue]With[/color]

 

[jay333]

Another method...

[COLOR=darkblue]With[/COLOR] Sheet1.Cells(2, 3)
    .ClearComments [COLOR=green]'Clear comment if already exists[/COLOR]
    .AddComment Sheet2.Cells(5, 6).Comment.Text
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]

This does work (sort of). I do get an error when the vba code runs although in the end it does put the comment into the cell. I'm thinking the error has something to do with the target sheet being protected or something?

 

[AlphaFrog]

The lines in red allow the macro to make changes to comments on the protected worksheetsheet. Change the password to suit.

With Sheet1.Cells(2, 3)
    [COLOR="Red"].Parent.Protect Password:="[COLOR="Blue"]Secret[/COLOR]", DrawingObjects:=False[/COLOR]
    .ClearComments 'Clear comment if already exists
    .AddComment Sheet2.Cells(5, 6).Comment.Text
    [COLOR="Red"].Parent.Protect Password:="[COLOR="Blue"]Secret[/COLOR]"[/COLOR]
End With

 

[jay333]

The lines in red allow the macro to make changes to comments on the protected worksheetsheet. Change the password to suit.

With Sheet1.Cells(2, 3)
    [COLOR=red].Parent.Protect Password:="[COLOR=blue]Secret[/COLOR]", DrawingObjects:=False[/COLOR]
    .ClearComments 'Clear comment if already exists
    .AddComment Sheet2.Cells(5, 6).Comment.Text
    [COLOR=red].Parent.Protect Password:="[COLOR=blue]Secret[/COLOR]"[/COLOR]
End With

Unfortunately, I am still getting an error. It is "Run-time erros '91', Object variable or With block variable not set". And, the '.AddComment' line is highlighted.

Any further thoughts?

 

[AlphaFrog]

Make sure Sheet2.Cells(5, 6) has a comment to copy.
Cell "F5"

 

[rediffusion]

AlphaFrog

And how to implement such:
1. I have 'comment' in the cell. In this cell there is some 'text'.
2. How to copy only 'comment' to another cell. In the other cell with only 'text' (but some records in another cell should not be overwritten)!
That is, I need Copy-Paste.

 

[Fluff]

@rediffusion
You have already been warned about cross-posting.
Please supply the links.