Copying comments to master sheet?

[jammin7800]

I've searched the board and not found what I'm looking for.

Here's my "Comments" issue.

I have a single workbook with 4 worksheets in it. This is a dimensional report sheet. We measure stuff and report our findings.

The first worksheet is the master sheet. The next three sheets are filled in by three different inspectors.

I've got a formula in all the appropriate cells in the master sheet that brings in values from the other 3 sheets. It uses an IF formula. If there is no value reported on any of the 3 inspection sheets, the master sheet displays the text "MISSING".

So, the Master Sheet is the compilation or summary sheet, if you will.

Is there a way to have the master sheet bring in not only the values, but also the comments of cells from the other 3 sheets? There will be no cell that has comments on more than one inspector sheet.

Right now, I'm using Copy -> Paste Special -> Comments. I'm looking to automate this, so that as soon as an inspector fills in a value and adds a comment, both items get put into the master sheet automatically.

 

[EugeneCarter]

K, this isn't coded well, but it'll give you an idea.

    Selection.ClearComments
    Selection.AddComment
    myReference = Selection.Formula
    myText = Range(myReference).Comment.Text
    With Selection
        .Comment.Visible = True
        .Comment.Text Text:=myText
    End With

There's chance for the cell to not have a formula, not have any comment, etc.

Sub MacroName ()<MACRO name>
the code goes here
end sub

Then assign the macro to a control-key or a button or whatever you wish.
I place macros in the worksheet that will use them, or if multiple worksheets, i will put them in personal.xls