Q: copying comments

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?

Also include:

Selection.PasteSpecial Paste:=xlPasteComments
--
Gary's Student


"JIM.H." wrote:

> 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?
>

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

"Gary''s Student" <GarysStudent@discussions.microsoft.com> schreef in
bericht news:A246E045-7A3F-429B-91CF-4F57A2541911@microsoft.com...
> Also include:
>
> Selection.PasteSpecial Paste:=xlPasteComments
> --
> Gary's Student
>
>
> "JIM.H." wrote:
>
>> 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?
>>

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.

"Gary''s Student" wrote:

> Also include:
>
> Selection.PasteSpecial Paste:=xlPasteComments
> --
> Gary's Student
>
>
> "JIM.H." wrote:
>
> > 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?
> >

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

"JIM.H." <JIMH@discussions.microsoft.com> wrote in message
news:EDF4206C-2C13-4C75-8930-B0BFC2FD8F77@microsoft.com...
> 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.
>
> "Gary''s Student" wrote:
>
> > Also include:
> >
> > Selection.PasteSpecial Paste:=xlPasteComments
> > --
> > Gary's Student
> >
> >
> > "JIM.H." wrote:
> >
> > > 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?
> > >