Anthony,
The following code should work. There are some limitations, which I added in as comments in the code. I apologize, but I just don't have time to work my through them right now.
I didn't know several particulars like whether the sheets are in the same workbook, what the sheet names would be etc. So I tried to write this to be as flexible as possible. Obviously, you could edit this to Set wsSource and wsTarget in the code for the user if desired. Note that if the worksheets are in different books, they'll need to look in All Open Workbooks for the macro.
Anyhow, I hope this helps.
<font face=Courier New><SPAN style="color:#00007F">Dim</SPAN> wsSource <SPAN style="color:#00007F">As</SPAN> Worksheet, wsTarget <SPAN style="color:#00007F">As</SPAN> Worksheet
<SPAN style="color:#00007F">Dim</SPAN> wbSource <SPAN style="color:#00007F">As</SPAN> Workbook, wbTarget <SPAN style="color:#00007F">As</SPAN> Workbook
<SPAN style="color:#00007F">Sub</SPAN> CopyComments()
<SPAN style="color:#007F00">' ---------------------------------------------------------------------</SPAN>
<SPAN style="color:#007F00">' Copies comments from one worksheet to another</SPAN>
<SPAN style="color:#007F00">' based on matching values in specified columns in both sheets</SPAN>
<SPAN style="color:#007F00">' LIMITATIONS:</SPAN>
<SPAN style="color:#007F00">' 1. The columns where the comments are located are the same</SPAN>
<SPAN style="color:#007F00">' on both worksheets.</SPAN>
<SPAN style="color:#007F00">' 2. The columns being matched on both sheets are the same on</SPAN>
<SPAN style="color:#007F00">' both worksheets.</SPAN>
<SPAN style="color:#007F00">' 3. Neither the matching columns range nor the comments columns range</SPAN>
<SPAN style="color:#007F00">' can comprise more than one area. (The columns must be side-by-side.)</SPAN>
<SPAN style="color:#007F00">' Example: You cannot set strcMatchCols = "A:A,C:D".</SPAN>
<SPAN style="color:#007F00">' - Greg</SPAN>
<SPAN style="color:#007F00">' ---------------------------------------------------------------------</SPAN>
<SPAN style="color:#00007F">Const</SPAN> strcCommentCols <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "E:F" <SPAN style="color:#007F00">' where the comments are</SPAN>
<SPAN style="color:#00007F">Const</SPAN> strcMatchCols <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "A:C" <SPAN style="color:#007F00">' columns that need to match up</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> strMsg <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, comSource <SPAN style="color:#00007F">As</SPAN> Comment
<SPAN style="color:#00007F">Dim</SPAN> rngSourceMatch <SPAN style="color:#00007F">As</SPAN> Range, rngTargetMatch <SPAN style="color:#00007F">As</SPAN> Range
<SPAN style="color:#00007F">Dim</SPAN> rngSourceComment <SPAN style="color:#00007F">As</SPAN> Range, rngTargetComment <SPAN style="color:#00007F">As</SPAN> Range
<SPAN style="color:#00007F">Dim</SPAN> rngSourceComments <SPAN style="color:#00007F">As</SPAN> Range, rngTargetComments <SPAN style="color:#00007F">As</SPAN> Range
<SPAN style="color:#00007F">Dim</SPAN> varValues() <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>, rngMatch <SPAN style="color:#00007F">As</SPAN> Range
<SPAN style="color:#00007F">Dim</SPAN> bytCol <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN>, booFound <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>, strFirstAddr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
<SPAN style="color:#00007F">If</SPAN> wsSource <SPAN style="color:#00007F">Is</SPAN> Nothing _
<SPAN style="color:#00007F">Or</SPAN> wsSource <SPAN style="color:#00007F">Is</SPAN> Nothing <SPAN style="color:#00007F">Then</SPAN>
strMsg = "Is Worksheet: " & ActiveSheet.Name & vbCr & _
"In Workbook: " & ActiveWorkbook.Name & vbCr & vbCr & _
"Where you want to copy comments FROM? " & vbCr & vbCr & _
"If not, click [NO] and select the correct sheet."
<SPAN style="color:#00007F">If</SPAN> vbNo = MsgBox(strMsg, vbQuestion + vbYesNo, "Confirm Source") <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
<SPAN style="color:#00007F">Set</SPAN> wsSource = ActiveSheet
<SPAN style="color:#00007F">Set</SPAN> wbSource = ActiveWorkbook
MsgBox "Now click on the sheet where you want to copy to.", vbInformation
<SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">If</SPAN> wsTarget <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> _
<SPAN style="color:#00007F">Or</SPAN> wsTarget <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN>
strMsg = "Is Worksheet: " & ActiveSheet.Name & vbCr & _
"In Workbook: " & ActiveWorkbook.Name & vbCr & vbCr & _
"Where you want to copy comments TO? " & vbCr & vbCr & _
"If not, click [NO] and select the correct sheet."
<SPAN style="color:#00007F">If</SPAN> vbNo = MsgBox(strMsg, vbQuestion + vbYesNo, "Confirm Target") <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
<SPAN style="color:#00007F">Set</SPAN> wsTarget = ActiveSheet
<SPAN style="color:#00007F">Set</SPAN> wbTarget = ActiveWorkbook
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
strMsg = "Source: " & wsSource.Name & " in " & wbSource.Name & vbCr & vbCr & _
"Target: " & wsTarget.Name & " in " & wbTarget.Name
<SPAN style="color:#00007F">If</SPAN> vbCancel = MsgBox(strMsg, vbOKCancel + vbQuestion, "Confirm Again Please") <SPAN style="color:#00007F">Then</SPAN>
<SPAN style="color:#00007F">Set</SPAN> wsSource = <SPAN style="color:#00007F">Nothing</SPAN>
<SPAN style="color:#00007F">Set</SPAN> wbSource = <SPAN style="color:#00007F">Nothing</SPAN>
<SPAN style="color:#00007F">Set</SPAN> wsTarget = <SPAN style="color:#00007F">Nothing</SPAN>
<SPAN style="color:#00007F">Set</SPAN> wbTarget = <SPAN style="color:#00007F"><SPAN style="color:#00007F">Not</SPAN>hing</SPAN>
<SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">Set</SPAN> rngSourceComments = wsSource.Range(strcCommentCols)
<SPAN style="color:#00007F">Set</SPAN> rngTargetComments = wsTarget.Range(strcCommentCols)
<SPAN style="color:#00007F">Set</SPAN> rngSourceMatch = wsSource.Range(strcMatchCols)
<SPAN style="color:#00007F">Set</SPAN> rngTargetMatch = wsTarget.Range(strcMatchCols)
<SPAN style="color:#00007F">ReDim</SPAN> varValues(rngSourceMatch.Columns.Count)
<SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> comSource <SPAN style="color:#00007F">In</SPAN> wsSource.Comments
<SPAN style="color:#00007F">If</SPAN> Not Intersect(comSource.Parent, rngSourceComments) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN>
<SPAN style="color:#00007F">Set</SPAN> rngMatch = <SPAN style="color:#00007F"><SPAN style="color:#00007F">Not</SPAN>hing</SPAN>
booFound = <SPAN style="color:#00007F">False</SPAN>
<SPAN style="color:#00007F">For</SPAN> bytCol = 1 <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(varValues)
varValues(bytCol) = rngSourceMatch(comSource.Parent.Row, bytCol)
<SPAN style="color:#00007F">Next</SPAN> bytCol
<SPAN style="color:#00007F">Set</SPAN> rngMatch = rngTargetMatch.Columns(1).Find(varValues(1), LookAt:=xlWhole, LookIn:=xlValues)
<SPAN style="color:#00007F">If</SPAN> Not rngMatch <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Not</SPAN>hing <SPAN style="color:#00007F">Then</SPAN> strFirstAddr = rngMatch.Address
<SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">While</SPAN> Not rngMatch <SPAN style="color:#00007F">Is</SPAN> Nothing
booFound = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">For</SPAN> bytCol = 2 <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(varValues)
<SPAN style="color:#00007F">If</SPAN> rngMatch.Offset(, bytCol - 1).Value <> varValues(bytCol) <SPAN style="color:#00007F">Then</SPAN>
booFound = <SPAN style="color:#00007F">False</SPAN>
<SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">For</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">Next</SPAN> bytCol
<SPAN style="color:#00007F">If</SPAN> booFound <SPAN style="color:#00007F">Then</SPAN>
<SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Do</SPAN>
<SPAN style="color:#00007F">Else</SPAN>
<SPAN style="color:#00007F">Set</SPAN> rngMatch = rngTargetMatch.Columns(1).FindNext(rngMatch)
<SPAN style="color:#00007F">If</SPAN> rngMatch.Address = strFirstAddr <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Do</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">Loop</SPAN>
<SPAN style="color:#00007F">If</SPAN> booFound <SPAN style="color:#00007F">Then</SPAN>
comSource.Parent.Copy
rngTargetComments.Cells(rngMatch.Row, comSource.Parent.Column - rngTargetComments.Columns(1).Column + 1).PasteSpecial _
Paste:=xlPasteComments, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=<SPAN style="color:#00007F">False</SPAN>
Application.CutCopyMode = <SPAN style="color:#00007F">False</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">Next</SPAN> comSource
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
Regards,