copying cell comment from one sheet to another

  • Thread starter Thread starter John Keith
  • Start date Start date
J

John Keith

I need to copy a cell value and a comment if it exists from one sheet
to another sheet in the same workbook. I tried the following code but
it fails on the if statement with the error message:

Object variable or With block variable not set

The code works fine if the source cell has a comment and only fails
when the source cell does not have a comment.

What do I need to add to the code to make this work correctly?

Sub copy_cell()
Worksheets("sheet2").Cells(1, 1) = Worksheets("sheet1").Cells(1,
1)
If Worksheets("sheet1").Cells(1, 1).Comment.Text <> "" Then
Worksheets("sheet2").Cells(1, 1).AddComment
Text:=Worksheets("sheet1").Cells(1, 1).Comment.Text
End If
End Sub

Thanks for looking.



John Keith
(e-mail address removed)
 
The below will copy the value and comment

Worksheets("sheet1").Cells(1, 1).Copy Worksheets("sheet2").Cells(1, 1)

Modified your macro to suit...

Sub copy_cell()
Worksheets("sheet2").Cells(1, 1) = Worksheets("sheet1").Cells(1, 1)
If Not Worksheets("sheet1").Cells(1, 1).Comment Is Nothing Then
Worksheets("sheet2").Cells(1, 1).AddComment
Text:=Worksheets("sheet1").Cells(1, 1).Comment.Text
End If
End Sub
 
Modified your macro to suit...
Click to expand...

Jacob,

Thank you that worked great.

For my education can you comment on why my original techique failed?


John Keith
(e-mail address removed)
 
Back
Top