You have carefully defined both the source and destination ranges. Why not
paste/special comments?:


Sub Macro1()
Range("D1").Select
Selection.Copy
Range("F5").Select
Selection.PasteSpecial Paste:=xlPasteComments
End Sub

Where you use your ranges in place of mine.

--
Gary's Student
gsnu200708


"Jay" wrote:

Im working on creating a report that will insert comments from a separate
workbook. Im clear on what I want to achieve, but Im sort of clueless on
how to get there. My destination worksheet will provide a Work Order number
that is the same in the source worksheet. The comments in the source
worksheet are in the same cell as the Work Order number itself.
Any help would be greatly appreciated.

Heres what Ive got so far (but its not working):

Sub Copy_Comment()

Dim SourcWbk As Workbook
Dim SourcRng1 As Range
Dim SourcCmt1 As Range
Dim WkOr As Range
Dim DestRng As Range
Dim cmt As Comment

Set SourcWbk = Workbooks.Open("H:\FAC\Drafting Work Queue2.xls")
On Error Resume Next
Set SourcRng1 = SourcWbk.Sheets("Work Orders").Range("A3:A200")
Set WkOr = ThisWorkbook.Sheets("Sheet1").Range("B4")
Set DestRng = ThisWorkbook.Sheets("Sheet1").Range("D4")
Set SourcCmt1 = WorksheetFunction.Offset(SrcRng1,_
(WorksheetFunction.Match(WkOr, SrcRng1, 0) - 1), 0, 1, 1)
Set cmt = SourcCmt1.Comment


DestRng.Value = cmt.Text


End Sub