I am glad you are having success!

to get a better-look comment, just replace

v = v & r.Value

with

v = v & Chr(10) & r.Value

this will produce a single column in the comment. Making two columns in the
comment is a little more tricky.
--
Gary''s Student


"Meltad" wrote:

> Thanks GS!
>
> Finally I've pasted something into a comment box!!
>
> Now another thing... do you know if I'm able to copy formats cos what Ive
> pasted is all jumbled etc. Almost need to be able to paste as a table?....
>
> Is this pushing my luck!!
>
> Thanks, Mel
>
>
>
> "Gary''s Student" wrote:
>
> > How about:
> >
> > Sub meltad()
> > Dim v As String
> > Dim r As Range
> >
> > Sheets("KPI values").Select
> > Range("B9:C50").Select
> > For Each r In Selection
> > v = v & r.Value
> > Next
> > Range("H11").AddComment
> > Range("H11").Comment.Text Text:=v
> > End Sub
> >
> >
> > You can also concatenate CHR(10)'s into v to improve the appearance.
> > --
> > Gary's Student
> >
> >
> > "Meltad" wrote:
> >
> > > Hi,
> > >
> > > I'm trying to copy a range in my worksheet then paste this to a single
> > > comment in a neighbouring cell.
> > >
> > > So far I've found this code for pasting from the clipboard to a comment
> > > (courtesey of Bob Greenblatt)....
> > >
> > > Sub InsertComment()
> > > Dim oComm As DataObject
> > > Set oComm = New DataObject
> > > With ActiveCell
> > > .AddComment
> > > oComm.GetFromClipboard
> > > .Comment.Text Text:=oComm.GetText(1)
> > > End With
> > > End Sub
> > >
> > >
> > > however I get an error (user type not defined) - so I must have left
> > > something out. Any ideas anyone??
> > >
> > > I want to...
> > >
> > > Range("H11").AddComment ""
> > > Sheets("KPI values").Select
> > > Range("B9:C50").Select
> > > Selection.Copy
> > >
> > > then paste this into the comment box.... ???
> > >
> > > THANKS, Mel
> > >