Help with VBA code to copy formatting into clipboard

[p85ki]

Hi all,

I am new here, I do alot in Excel and have taken my hand in VBA (slowly) and learning. Currently I am stuck and have no idea how to get this to work in the way I want. I am reading but there's soo much information I really am getting lost with it.

Please see below in what

How it's currently working

• I have some text within a few cells.
• This text is displayed once a user has selected a few filters.
• I have a formula which reads how many cells will provide me text so it's a little dynamic. The rule of thumb is all text will always be within cells A3-A11.
• The text will display in A3,A5,A7,A9,A11 BUT there's never any text in A2,A4,A6,A8 and A10.
• The reason behind this is so that when I press the "Copy Text" button I have created, it copies those ranges of cells which will include the spacing between them.
• When I press the "Copy Text" button this copies and pastes into a Notepad.
• This allows the text to display in the Notepad as new sentences and not just a line under the other (or quotation marks at the start/end of the text).

This use to be a great solution, but now I really need to improve this but not sure how.

The issue with the above

• The first cell is always formatted and 1 of the other cell is also formatted. How I do it at the moment doesn't copy the formatting as it is, which means I have to always add this in.
• Skipping a cell is only trying to ensure I can copy this into a notepad, ideally I just want to put this one under the other.
• When this copies into the Notepad, it also has large white spaces that shouldn't be there.
• The reason I paste this into a Notepad is to remove the "Table" cells, otherwise if this was paste directly in Word 2013 (then the table cells will show invisible).
• The way this works is no longer practical and need a better solution.

• The information is not being copied from one spreadsheet to another, this is being copied from a spreadsheet to an online form (which the form url is dynamic so unable to link this to that).

What I need help with

• To copy each one of those cells and keep the formatting/font to whatever is selected as it is (i.e. where I have it in bold that is remains in bold).
• Where I have the information in a new cell, it creates 2 line breaks and doesn't copy it as a table (as if it's a cell).
• That it removes any spacing at the beginning and end of the sentence.
• This copies into a clipboard and confirmation message to say text has been copied to clipboard.
• Naturally if it's copied, then this should paste the information onto the online documentation with the formatting as it is.

Here is the standard code I am currently using:

Private Sub CopyProblemText_Click()With Application

' Selects Range you want to do something with
   Range("$A$3:$A$11").Select

' Copies the Range you selected
    Selection.Copy

Selection.PasteSpecial xlPasteFormats

' Command to open Notepad
'Shell "Notepad.exe", 3

' Uses the CTRL+V to paste into Notepad
SendKeys "^v"

VBA.AppActivate .Caption
.CutCopyMode = False
End With
End Sub

I really have gone round the houses with this but cannot find a solution.

Can anyone provide any assistance, either the code required or help with understanding this (is this really soo complex to do).

Thank you in advanced.

[Leith Ross]

Hello p85ki,

After reading your post, I am not sure I am any closer to understanding what it is you want to do. I get you have a group of cells on the worksheet and that they are to be pasted into another document. You mention both Notepad and "online documentation". Does the latter imply a web document?

[p85ki]

Hi Leith

Thank you for your message.

Sorry I tried to give as much detail which may have confused things.

I simply want to copy the text with all the formatting (bold) into a clipboard.

With each cell that there is text, I want to have 2 line breaks. For example:

CELL A3: [B]Progression update 19/08/2018[\B]
A4: As this can be replicated, we are treating this as a generic issue.
A5: more standardised text here.
A6: more jargon...

So cell A3 to have the bold format.
Cell A4 to have bold around generic.

Copy all this into a clipboard.
This is so I can paste this into an online document.

Thanks

[Leith Ross]

Hello p85ki,

Text copied to the clipboard has no formatting. The application adds the formatting to the text. What is the online application?

[p85ki]

Is it possible to store it into memory to allow formatting to be paste into a form?

The online form is an internal site where we write information that gets sent to developers before going to our customers.

[Leith Ross]

Hello p85ki,

There are a couple of options. You can save the data to the clipboard and paste it into the form, which is the easiest or use a more advanced approach to paste the text from the cells into the document using VBA and HTML. Since you are using an internal site (intranet) the latter is not really a viable option because of site restrictions, etc.

Copy and paste this code into a new VBA Module in your workbook. Execute the "Run" macro from the macro dialog by pressing the Alt+F8 keys and double clicking Run.
This will copy the values from A3:A6 of the active sheet to the clipboard as text. You can change the range if you need to.

Function AddForms20Lib() As Boolean


    ' NOTE: With Excel 2007 and later, you must change your security settings
    '       to "Trust access to the VBA project object model" before this macro
    '       will run.
    '
    ' Written: September 27, 2010
    ' Author:  Leith Ross www.excelforum.com
    
    Dim Reference As Object
    Dim OLB As String
    Dim vMajor, vMinor
    
      ' FM20 Type Library
        OLB = "{0D452EE1-E08F-101A-852E-02608C4D0BB4}"
        vMajor = 2
        vMinor = 0
     
        On Error Resume Next
        ThisWorkbook.VBProject.References.AddFromGuid OLB, vMajor, vMinor
        
        If Err <> 0 And Err <> 32813 Then
            AddForms20Lib = False
        Else
            AddForms20Lib = True
        End If
     
End Function




Sub CopyTextToClipboard(ByRef Rng As Range)


    Dim Cell    As Range
    Dim DataObj As Object
    Dim Text    As String
    
        If AddForms20Lib = False Then
            MsgBox "VBE Object library not loaded." & vbCrLf & Err.Description
            Exit Sub
        End If
        
        Set DataObj = MSForms.DataObject
        
        For Each Cell In Rng.Cells
            Text = Text & Cell.Value & vbCrLf
        Next Cell
        
        DataObj.Clear
        DataObj.SetText Text
        DataObj.PutInClipboard
        
End Sub


Sub Run()


    Call CopyTextToClipboard(Range("A3:A6"))
    
End Sub

[p85ki]

Thank you Leith

That's great. I'll give this a go later today and give you an update.

In all honesty I probably wouldn't have got to that answer alone as wouldn't of known about the first part of the code you did.

Just want to check though (I assume not) that it's not possible to copy with the formatting? If not then this is probably the best I will be able to get.

Thanks again

[Leith Ross]

Hello p85ki,

The clipboard will hold the ANSI or UNICODE values for the plain text. The only text I am aware of that could contain formatting would be in Rich Text Format (RTF). This is used by Office and some third party applications but the web standard does not support RTF. Personally, I have never seen or interacted with a site that required text to be input in a specific style or font.