Macro to get comments from a range of cells

[im4floridagtrz]

All,

I am looking for a code that will take comments from multiple cells within a row and return the product into one cell in a difrrent worksheet. I have tried everything with no luck. Can someone please help me out?

Thanks

 

[VoG]

Try like this

Sub cmts()
Dim cmt As Comment, msg As String
With Sheets("Sheet1")
    For Each cmt In .Comments
        msg = msg & vbNewLine & cmt.Parent.NoteText
    Next cmt
End With
Sheets("Sheet2").Range("A1").Value = msg
End Sub

 

[im4floridagtrz]

Thanks for the reply! That code sorta worked, it put all the cooments from the whole workbook into one cell. Let me explain further what I am looking for. I am looking to take all of the comments from E2:E309 and put them into cell H2 on a diffrent workbook. I need comments from F2:F309 to go into H3, and so on.

Thanks

 

[VoG]

Try like this

Sub cmts()
Dim c As Range
For Each c In Sheets("Sheet1").Range("E2:E309")
    If Not c.Comment Is Nothing Then Sheets("Sheet2").Range("H" & Rows.Count).End(xlUp).Offset(1).Value = c.NoteText
Next c
End Sub

 

[im4floridagtrz]

Thanks that works except for it only runs on column "E". What command will make it automatically go to "F" and so on? Or do I have to copy and paste for every row I want?

 

[VoG]

Try

Sub cmts()
Dim c As Range
For Each c In Sheets("Sheet1").Range("E2:F309")
    If Not c.Comment Is Nothing Then Sheets("Sheet2").Range("H" & Rows.Count).End(xlUp).Offset(1).Value = c.NoteText
Next c
End Sub

Change F to the rightmost column that you want to look at.

 

[im4floridagtrz]

Gotcha. thanks for your help!