title: VBA Excel Copying comments and paste to another cell
body: <p>I am pretty new to VBA - but I am trying to write a function to copy a cell and paste the comments to another cell.  I have done quite a bit of searching and haven't found and answer and am too new to VBA to understand how to modify what I did find.  Background is I have a report that is pulled from another company that puts images into the comments, but I want to take that report and put it in an easier to read format, without manually copying and pasting things all the time. I tried a pivot table but that doesn't return the values, so now trying to write a VBA function to use with an index match to populate my tracker with the data.</p>
  
  <p>I found a Macro that does exactly what I want, listed below.  However I want to use it as function so I can use if statements or an index match.  </p>
  
  <pre><code>Sub CommentCopy()
  Range("Q7").Copy
  Range("Q8").PasteSpecial xlPasteComments
  Application.CutCopyMode = False
  End Sub
  </code></pre>
  
  <p>I also found a function that works that copies the text of comments and pastes it to the adjacent cell which is ot what I want</p>
  
  <pre><code>Function commentof(r As Range) As String
  Application.Volatile
  If r.Comment Is Nothing Then
      commentof = ""
  Else
      commentof = r.Comment.Text
  End If
  End Function
  </code></pre>
  
  <p>So I tried to combine the two into this but it is not compiling:</p>
  
  <pre><code>Function commentpaste(r As Range) As String
  r.Copy
  comment paste = PasteSpecial xlPasteComments
  Application.CutCopyMode = False
  End Function
  </code></pre>
  
  <p>I also found this link where it talks about copying to a clipboard in a function not being possible? So am I actually not able to do this? <a href="https://stackoverflow.com/questions/17552220/need-guidance-with-a-vba-function-to-paste-values-in-excel">Need guidance with a VBA function to paste values in Excel</a> </p>
  
  <p>Thank you to everyone in advance for your help!
  Brendan</p>