Q: copying comments

[Guest]

Hello,

The following code lets me copy a range from an excel file to another

Set CopyRange = Union(Range(Cells(1, 1), Cells(ActiveCell.Row,
numOfColumnSkip)), Range(Cells(1, ActiveCell.Column), Cells(ActiveCell.Row,
ActiveCell.Column)))

CopyRange.Copy
Workbooks.Add
'ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlValues
Selection.PasteSpecial Paste:=xlFormats
Application.CutCopyMode = False


Now; this does not copy the comments. I need to copy the comment in cell
Cells(ActiveCell.Row, numOfColumnSkip)) to the end of new workbook, not as a
comment but as a textbox so that I can copy it to another application with
Control+C and Control+V. Is this possible?

 

[Guest]

Also include:

Selection.PasteSpecial Paste:=xlPasteComments

 

[Jack Sons]

GS,

OP asked for code to place a textbox on the sheet with in it the text of the
comment of each copied cell of his range. A ragen with relatively few cells,
I think, or else you get a chaos of textboxes.

I find this mighty interesting, could be useful. Please shed your light on
it.

Jack Sons

 

[Guest]

Thanks, that worked ok, however after I created this new workbook I use
Control+C and Control+V to copy all the information from that sheet to
Outlook Express to e-mail. Now Control + C does not bring comments from excel
to OE, that is why I was asking if there is any way to copy it to a textbox
so that I can copy it to OE.

 

[Chris Lavender]

Hi Jim

You need to refer to Cells(ActiveCell.Row, numOfColumnSkip).Comment.Text

eg
myval = oldsheet.Cells(ActiveCell.Row, numOfColumnSkip).Comment.Text

newsheet.range("A1").value = myval

HTH
Chris Lav