I'm trying to put together an attendance sheet and where comments placed on one worksheet, automatically copies to the second worksheet based on two criteria: name and date. I've attached a copy of an example worksheet. I would like to have it so that when a comment is placed on Sheet 2 under Jamie Lee and February 10th (E5), would show up on the corresponding calendar under the name of the employee and date on Sheet 1 (O7).
I tried looking through multiple forums and threads, but no luck. Thank you in advance for you help!
This is what I got at the moment:
Option Explicit Sub CopyComments() Dim lCurRow As Long Dim lHit As Long Dim shtSource As Worksheet Dim shtDest As Worksheet Dim cmt As Comment Dim zHoldCmt As String Dim var1 As Integer Dim var2 As Integer Set shtDest = ActiveWorkbook.Sheets("Sheet1") Set shtSource = ActiveWorkbook.Sheets("Sheet2") Do On Error Resume Next lCurRow = WorksheetFunction.Match(shtDest.Range("C6"), shtSource.Range("E4:J4"), 0) lHit = WorksheetFunction.Match(shtDest.Range("C2"), shtSource.Range("B5:B100"), 0) On Error GoTo 0 If lHit > 0 Then Set cmt = shtSource.Cells(lHit, lCurRow).Comment If Not (cmt Is Nothing) Then zHoldCmt = shtSource.Cells(lHit, lCurRow).Comment.Text Set cmt = shtDest.Cells(lCurRow, lHit).Comment If (cmt Is Nothing) Then Set cmt = shtDest.Cells(lCurRow, lHit).AddComment End If cmt.Text Text:=zHoldCmt End If lHit = 0 End If lCurRow = lCurRow + 1 Loop Until Cells(lCurRow, lHit) = "" End Sub