This will copy a range to the clipboard to be pasted elsewhere. I needed it to copy my plant notes into cms from my plant note template.
If you paste into notepad or other text editors it all goes onto one line. However, it works correctly in CMS, with line returns where they should be.
Sub CopyMe2Clipboard() Application.Goto Reference:="CopyMe" Call SelectionToClipboard End Sub
'I got this from an XLA that was apparently intended to copy a selection to a HTML table or something 'It was called Confluence and is by philbogle at gmail
Dim specialChars As String specialChars = Chr(7) & Chr(10) & Chr(13) & "\"
Dim result As Variant Dim cellStr As String Dim i, j As Integer result = "" For i = 1 To Selection.Rows.Count result = result ' & "| " He formatted this with borders, I don't want For j = 1 To Selection.Columns.Count Dim cell As Range Set cell = Selection.Cells(i, j) cellStr = cell.Text If Not IsEmpty(cellStr) And cellStr <> "" Then Do While InStr(specialChars, Right(cellStr, 1)) > 0 cellStr = Left(cellStr, Len(cellStr) - 1) Loop End If cellStr = Replace(cellStr, Chr(13), "\\" + Chr(13)) cellStr = Replace(cellStr, Chr(10), "\\" + Chr(13))
If cell.Font.Bold Then cellStr = "*" & cellStr & "*" If cell.Font.Italic Then cellStr = "_" & cellStr & "_" result = result & cellStr ' & " | " He formatted this with borders, I don't want Next result = result & Chr(13) Next Dim MyDataObj As New DataObject MyDataObj.SetText result MyDataObj.PutInClipboard
MsgBox "Plant notes copied to clipboard: " & Chr(13) & Chr(13) & result End Sub