Link Comments from Another Workbook

[Dom1878]

Hi,

I currently have a file which has figures and comments, which need to be linked to another workbook. I've pasted as links and that works for the cells however the comments don't carry over. In an effort to do this, i've created some VBA I thought may be useful.

I created the function:

Public Function FetchComment(cell As Range) As String
Dim CellComment As Comment


Application.Volatile False


Set CellComment = cell.Comment


If CellComment Is Nothing Then
FetchComment = ""
Exit Function
End If


If CellComment.Text = "" Then
FetchComment = ""
Else
FetchComment = cell.Comment.Text
End If


End Function



To extract the comment from a given location. The plan was to paste the solution to this into a comment of the activecell.

So basically the VBA goes through cells A1:Z20 on the workbook I want - which has all been linked to the workbook I want to copy the notes from, so it uses the link in the active cell to extract the notes and paste into the comment box. However, the function will not work as it needs a range and I currently have a string as its an address. So the VBA at the moment is:

Sub Comments()
Dim rng As Range
Dim cell As Variant
Dim MyString As String
Dim Range1 As Range


Set rng = Range("A1:Z20")


For Each cell In rng
With cell.Select
ActiveCell.ClearNotes

With ActiveCell
.AddComment (FetchComment(Right((ActiveCell.Formula), Len(ActiveCell.Formula) - 1))
End With
End With
Next


End Sub





At the moment Right((ActiveCell.Formula), Len(ActiveCell.Formula) - 1) is equal to C:\Users\****\Documents\[TestActive.xlsm]Sheet1'!A1

Any help would be appreciated as I'm a beginner when it comes to VBA

Thanks,

Dom