Thursday, July 7, 2011

Copy Text and Image Comment From one workbook to another workbook

You want to match the values and the copy the text or image comment from matched cell to other cell.

For Example -

I have taken two workbooks "Database" and "add comments". Database workbook is having the records with comments in column A. i want to match the records in 'add comments" workbook with database workbook and copy the comment of cell from workbook database to Add comments workbook for matched records.

Here is the code-

Sub abc()
Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim i As Long
Dim s As Workbook

Dim a As Range
Set s = Workbooks.Open(Application.GetOpenFilename)
Dim SrchRnga

Set SrchRnga = s.Sheets(1).Range("a:a")

For i = 2 To ThisWorkbook.Sheets(1).Range("a65356").End(xlUp).Row

Set a = SrchRnga.Find(ThisWorkbook.Sheets(1).Range("a" & i).Value, LookIn:=xlValues)

If Not a Is Nothing Then

s.Sheets(1).Range("a" & a.Row).Copy


ThisWorkbook.Sheets(1).Range("a" & i).Select

Selection.PasteSpecial Paste:=xlPasteComments
End If

Next i


Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

Excel Files

No comments:

Post a Comment