Cell Comments through Reference

[maheshmaxi]

Hi,

Is there any way to capture a cell comment to a different sheet or file through reference.

Ex: Sheet1 contains data in A1 with cell comment "pending", i need the same content in Sheet2 A1 with cell comment.

Can anyone help me on this?

 

[Sektor]

Sub CopyComments()

    Dim cell As Range, rng As Range
    Dim sh1 As Worksheet, sh2 As Worksheet

    Set sh1 = Worksheets("Sheet1")
    Set sh2 = Worksheets("Sheet2")
    
    For Each cell In sh1.UsedRange
        If Not cell.Comment Is Nothing Then
            sh2.Range(cell.Address(False, False, xlA1)).AddComment cell.Comment.Text
        End If
    Next
    
End Sub