How to copy only values in Excel?

How do copy just text values of cells in Excel

• I have a simple copy button in a worksheet with the code. Range("A9:E19").Select Selection.Copy I've noticed recently that when pasting the result into an email I get the cells too and not just the text values. I've read a lot of posts relating to pasting values back into excel but as I'm pasting to external email application (ie Gmail) is there a way of controlling what is actually sent to clipboard as opposed to filtering what is pasted which I've seen examples of. Sub Button1_Click() Option Explicit End Sub Sub Macro1() ' ' Macro1 ''Copy some Range Range("A9:E19").CurrentRegion.Copy 'Convert it to text and put in clipboard With dOBJ .GetFromClipboard S = .GetText .Clear .SetText S, 1 .PutInClipboard End With End Sub 'Set Reference to Microsoft Forms 2.0 Object Library Sub CopyText() Dim dOBJ As DataObject Dim S As String Set dOBJ = New DataObject 'Copy some Range 'Range("a1").CurrentRegion.Copy End Sub

• Answer:

  The following method, using the DataObject object, might be helpful. The DataObject is in the Forms Library, and I used early-binding, but you could use late-binding if you are going to distribute this. It seems to paste plain text using a normal paste in other applications. Option Explicit 'Set Reference to Microsoft Forms 2.0 Object Library Sub CopyText() Dim dOBJ As DataObject Dim S As String Set dOBJ = New DataObject 'Copy some Range Range("a1").CurrentRegion.Copy 'Convert it to text and put in clipboard With dOBJ .GetFromClipboard S = .GetText .Clear .SetText S, 1 .PutInClipboard End With End Sub Given the code you've posted above, one method of incorporating it into your macro, would be to use the following for the "button" code:= Option Explicit Private Sub CommandButton1_Click() 'Set Reference to Microsoft Forms 2.0 Object Library Dim dOBJ As DataObject Dim S As String Range("A19:E19").CurrentRegion.Copy Set dOBJ = New DataObject With dOBJ .GetFromClipboard S = .GetText .Clear .SetText S, 1 .PutInClipboard End With End Sub EDIT Examination of the OP's workbook example reveals that the problem is that there are merged cells, in which the non-visible cells still contain data. The contents is being picked up by the copy operation. One solution would be to unmerge and remerge the merged cells to get rid of the extraneous data, prior to doing the copy operation. What affect this would have on the workbook as a whole is unknown. If the above is a viable solution, then doing a simple copy, and a paste text into gMail, would be an easy solution. If editing of the merged cells to eliminate the "hidden" data will mess up the workbook, then one could copy to a scratch sheet and make the changes there, before copying to the clipboard.