Macro to copy cell comments to a new cell?

I have a 75x800 cell spreadsheet that has comments attached to many cells. I need to copy the contents of those comments to new cells at the end of the row they appear in:
- Some rows will have no cells with comments and some will have up to five cells with comments.
- I need to know what cell the comment originally came from when I am reading the comment in the newly created cell: Ideally, the new cell would have the original cell number followed by the comment, e.g. New Cell C76 would contain the comments attached to cell C4 and start with "From C4: ..."
- The comments then need to be deleted from the original cells.

I am using Excel 2003 on an XP-Pro machine.

Thanks very much in advance for all of your time and assistance!

Regards,

Guy


p.s. And if you're REALLY bored and have nothing else to do except to solve my problems , is there is a way to color code the cells so both the original cell and the new comment cell are the same color?

Hello MyBrainhURTS,

Welcome to the Forum!

This macro will find all the comments in each row from columns "A" to "BW", and copy start copying them to column "BX" or 76. The original cell address will be added to the comment and the cells will colored bright yellow.

Copy Comments and Highlight Cells Macro

Sub CopyComments()

  Dim C As Long
  Dim Cmmnt As Comment
  Dim R As Long
  Dim Rng As Range
  
    For R = 1 To ActiveSheet.UsedRange.Rows.Count
      Set Rng = ActiveSheet.Rows(R)
      On Error Resume Next
        Set Rng = Rng.SpecialCells(xlCellTypeComments)
        If Err = 1004 Then
          Err.Clear
        Else
          C = 10
          For Each Cell In Rng
            Cells(Rng.Row, C).AddComment Text:="From " & Cell.Address & vbLf & Cell.Comment.Text
            Cell.Interior.ColorIndex = 6
            Cells(Rng.Row, C).Interior.ColorIndex = 6
            C = C + 1
          Next Cell
        End If
      On Error GoTo 0
    Next R
    
End Sub

Adding the Macro
1. Copy the macro above pressing the keys CTRL+C
2. Open your workbook
3. Press the keys ALT+F11 to open the Visual Basic Editor
4. Press the keys ALT+I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL+V
7. Make any custom changes to the macro if needed at this time
8. Save the Macro by pressing the keys CTRL+S
9. Press the keys ALT+Q to exit the Editor, and return to Excel.

To Run the Macro...
To run the macro from Excel, open the workbook, and press ALT+F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sincerely,
Leith Ross

Leith:

WOO-HOO! You just saved me about 4 months of drudgery - You Rock!

Thanks much,

Guy

Hello MyBrainhURTS,

Glad to hear it works the way you wanted it to. I was keeping my fingers crossed.

Sincerely,
Leith Ross

Nice work, Leith.

Hi,

I wonder if you can help and chang this a little for me, I am looking to copy eny comments in Column E in sheet2 and copy them all into sheet 1 on cell E12 - anybody help with this please??