Comments

[Viking88]

I have about 9 spreadsheets that are all linked into one master spreadsheet. Is there a way I can make the comments come over as well with out having to copy and paste special comments?

 

[hatman]

I think you will need a UDF... something like

Public Function Get_Comment(rnge As Range) As String

    Get_Comment = Replace(rnge.Comment.Text, Chr(10), "")
    
End Function

 

[Viking88]

Thanks, but the only problem is that the comment would actually overwrite the current formula in the cell.

Is there a way I can pull in the comment so it would show as if I inserted it fresh with the red arrow in the corner?

 

[hatman]

What scope are you looking for? Are you simply trying to paste all, incluing comments in one click? Or are you intending to copy and paste ALL comments from an entire workbook into another one at a button click? Either is possible with VBA, as well as anything in between...

 

[Viking88]

In a perfect scenario I would like to leave the formulas alone and just have the ALL comments copy over from other work books.

 

[Viking88]

One other thing; since I will be getting comments from 9 other workbooks is there a way that once the comments are pasted into the master workbook that will not be overwritten by the next workbook's comments.

 

[hatman]

Well, here is some quick and dirty code get you going... might have some landmines in it, as I only did a quick test

Sub cmmnts()

    Dim wb As Workbook
    Dim sht As Worksheet
    Dim sht2 As Worksheet
    Dim cmt As Comment
    Dim cmt2 As Comment
    
    
    'assuming that the source workbook is currently open
    
    Set wb = Workbooks("book1")
    
    For Each sht In wb.Sheets
    
        On Error Resume Next
        
        Set sht2 = ActiveWorkbook.Sheets(sht.Name)
        
        On Error GoTo 0
        
        If Not sht2 Is Nothing Then
    
            For Each cmt In sht.Comments
            
                With sht2.Range(cmt.Parent.Address)
            
                    Set cmt2 = .Comment
                    
                    If cmt2 Is Nothing Then
                    
                        .AddComment
                
                        .Comment.Text cmt.Text
                        
                    End If
                    
                    Set cmt2 = Nothing
                
                End With
            
            Next cmt
        
            Set sht2 = Nothing
            
        End If
        
    Next sht
    
    Set wb = Nothing    

End Sub