A macro will do what it is told, I doubt that you want a macro that
only works between two specific sheets and with only one range
if that was all you wanted you could use Copy, then Edit, Paste special, comments

Okay I see, you want a formula to copy the comments, and
you cannot do that, because a formula can only obtain a value
it cannot format any cell including itself.

You could conceivably have the macro look at the formula and copy the comments
if it is a simple assignment, but that would be unreliable, because
you would have to run the macro.

Can you work with the original page and use filtering.

Or copy the entire page and delete rows you do not want.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"RJS" wrote in message oups.com...
Thanks - to clarify what I need to do:

Say, for simplicity, I have a set of values in 10 cells (A1:A10) in
sheet1 of a workbook which
also has comments in each cell. If I have another worksheet
(worksheet2) and want to replicate this column of values, I just enter
"=Sheet1!A1" etc. But this does not also return the comments.

I want references to return both the values and the comments (as
comments).

My real spreadsheet is a larger one, hence why I want this to be
automatic.

I am a VB novice although follow the basics so I could follow some
clear guidance - I think!

Thanks