Pages

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.Activate

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

Selection.PasteSpecial Paste:=xlPasteComments
End If

Next i

s.Close

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

Excel Files

http://www.filefactory.com/file/cc388c0/n/Database.xls

http://www.filefactory.com/file/cc388cf/n/add_comments.xls

No comments:

Post a Comment