Comments

Viking88

Board Regular
Joined
Apr 18, 2003
Messages
103
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?
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

hatman

Well-known Member
Joined
Apr 8, 2005
Messages
2,664
I think you will need a UDF... something like

Code:
Public Function Get_Comment(rnge As Range) As String

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

Viking88

Board Regular
Joined
Apr 18, 2003
Messages
103
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

Well-known Member
Joined
Apr 8, 2005
Messages
2,664
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

Board Regular
Joined
Apr 18, 2003
Messages
103

ADVERTISEMENT

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

Board Regular
Joined
Apr 18, 2003
Messages
103
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-known Member
Joined
Apr 8, 2005
Messages
2,664
Well, here is some quick and dirty code get you going... might have some landmines in it, as I only did a quick test

Code:
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
 

Forum statistics

Threads
1,141,720
Messages
5,708,090
Members
421,546
Latest member
delatollas

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top