Copy Comments from one Worksheet to another Worksheet based on two criteria

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:

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

forum.ozgrid.com/index.php?attachment/73446/

Regards,
Jason

Re: Copy Comments from one Worksheet to another Worksheet based on two criteria

Hi Carim, thank you for your response! Sorry, yes the comments would only be entered under the check in columns of each date on Sheet 2.

Sincerely,
Jason

Re: Copy Comments from one Worksheet to another Worksheet based on two criteria

Hi Carim,

I was hoping to make it for a full year, but only used half a year for demonstration. Standard weekends are Saturdays and Sundays, but I was hoping that the worksheet would be usable all days of the week.

Thank you in advance for your help.

Regards,
Jason

PS. Thanks for pointing out the duplicate thread I had, I've asked the MODs to delete it.

Re: Copy Comments from one Worksheet to another Worksheet based on two criteria

Hi Carim,

Originally, my intent was to only use the check in column for comments. However, I'm definitely open to suggestions from experts like yourself!

I appreciate the prompt response.

Sincerely,
Jason

Re: Copy Comments from one Worksheet to another Worksheet based on two criteria

Hi Carim,

What a difference some colour makes, that looks great! Thank you!

Cheers,
Gary

Re: Copy Comments from one Worksheet to another Worksheet based on two criteria

Ah yes! I do see that, thank you for that suggestion. That is a good idea to have that there!

Re: Copy Comments from one Worksheet to another Worksheet based on two criteria

Quote from Carim;797929

OK then ...

For any given day, say you have added a Comment to any cell ...

What should happen when you select the employee in Sheet 1 ?

Please note you have not answered my question about the handling difference you need between Check In and Check Out comments for the same day ...

Display More

Hi Carim,

I hope you had a good day.

How I envisioned the worksheet to work is:
If I place a comment on Sheet 2 for cell E5 (Jamie Lee, January 1) under the check in column, I would like to see that comment show up on Sheet 1 for cell C6 when I select Jamie Lee for C2 on Sheet 1.

Sheet 2:
[ATTACH=CONFIG]73502[/ATTACH]

Sheet 1:
[ATTACH=CONFIG]73503[/ATTACH]

Thank you for your continuing help with this.

Sincerely,
Jason

Re: Copy Comments from one Worksheet to another Worksheet based on two criteria

Hi Carim,

Thank you for the prompt response. I think the only way to solve it right now is to have the user only use the check in column for comments. Would that work?

Sincerely,
Jason

Re: Copy Comments from one Worksheet to another Worksheet based on two criteria

Thank you. I've been trying to change up my coding, I was able to get the comment to copy, but it's not pasting it correctly.

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 i         As Integer
      
   Set shtDest = ActiveWorkbook.Sheets("Sheet1")
   Set shtSource = ActiveWorkbook.Sheets("Sheet2")
 
   For i = 5 To 100
     lHit = WorksheetFunction.Match(shtDest.Range("AI12"), shtSource.Range("A4:J4"), 0)
     lCurRow = WorksheetFunction.Match(shtDest.Range("C2"), shtSource.Range("B1:B100"), 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 + 1
       
     End If
     
     lCurRow = lCurRow + 1
     
   Next i
   
End Sub

Re: Copy Comments from one Worksheet to another Worksheet based on two criteria

Quote from Carim;798105

Hello again,

Attached is your Version 3 ... to be tested ...

Hope this will help

Carim,

You are a LEGEND! Thank you so much for your help, I really appreciate it! This is exactly how I envisioned it to work and I didn't think it was possible.

I'm so intrigued by your algorithm, it's so impressive what you can accomplish with Excel. I still have a long ways to go to learn, but just following your posts, I have a little bit more understanding. I hope others who are looking for this will learn something as well.

Thank you, thank you and thank you!! Repped!

Sincerely,
Jason