Hi All,
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:
Code
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
Display More
forum.ozgrid.com/index.php?attachment/73446/
Regards,
Jason