Copy Active Cell Comment to next empty row on another worksheet

[timlh42]

I am trying to find a way to automatically copy any cell comment from worksheet1 in range H7:H5 as text to the next empty row on worksheet2

Is there any way to do this?

 

[DanteAmor]

Try this

Change data in red for your information

Sub Copy_Comment()
  Dim c As Range
  For Each c In Sheets("[COLOR=#ff0000]Sheet1[/COLOR]").Range("[COLOR=#ff0000]H5:H7[/COLOR]")
    If Not c.Comment Is Nothing Then Sheets("[COLOR=#ff0000]Sheet2[/COLOR]").Range("[COLOR=#ff0000]A[/COLOR]" & Rows.Count).End(xlUp)(2).Value = c.Comment.Text
  Next
End Sub

 

[timlh42]

Thank you! You are a genius! I do have one more thing. Is there any way to add the contents of the cell 3 rows above the comment on sheet1 to sheet2 column B?

 

[DanteAmor]

Try this

Sub Copy_Comment()
  Dim c As Range, lr as long
lr = sheets("sheet2").range("A" & rows.count).end(xlup).row + 1
  For Each c In Sheets("Sheet1").Range("H5:H7")
    If Not c.Comment Is Nothing Then Sheets("Sheet2").Range("A" & lr).Value = c.Comment.Text
Sheets("sheet2").Range("B" & lr).Value = c.offset(-3).Value
lr = lr + 1
End If
  Next
End Sub

 

[timlh42]

You never cease to amaze me. You make it look so easy. Thank you so much!!!
One last thing....... is there any way that I can name worksheets based on cell values?

I would like to have names listed in sheet1 in column A, beginning at A3 and ending on A30. So if there is a name in A3 ( George) and (Sam) in A4, (John) in A5 and so on, I would like it to change the sheet names from Sheet2 to George, sheet3 to Sam, sheet4 to John, etc

Is this possible?

 

[DanteAmor]

Because it is a different topic to this thread, please, you could create a new thread.

 

[timlh42]

Will do. Thank you!

 

[DanteAmor]

Youre welcome