How to paste text with bullets, bold characters and break lines into a single excel cell using VBA code?

Question

I want to do the equivalent of selecting a cell in a sheet, pressing F2 and then CTRL+V, so that all the formatting such as bullets, bold characters and breaklines are preserved and pasted into one cell. Please note that I'm NOT copying from excel sheets, rather im using excel vba to copy from a Word Document.

I've tried:

Sheets(1).Cells(1,1).Select
SendKeys "{F2}", True
SendKeys "v", True
Application.Wait(Now + TimeValue("0:00:5"))
Sheets(1).Cells(1,2).Select

But the above code pastes in the cell(1,2) of the active sheet instead of (1,1). Please suggest an alternative method for this.

Answer 1

To copy and paste a cell "preserving the formatting" (ie bold, underlines, text column, newlines, special characters (like bullet points) etc. You could use record macro to create code like the following which is simple copy, paste. You do not need to press F2 (as this will only give you access to the raw value - not the formatting):

Range("C3").Select
Selection.Copy
Range("D6").Select
ActiveSheet.Paste

modifying this example for you needs:

With Sheets(1)
    .Cells(1, 1).Copy
    .Cells(1, 2).Select
    .Paste
end with 

Answer 2

SendKeys "^v", True

This will send Ctrl+v. You shouldn't need the Wait command because the {True} argument for {SendKeys} tells Excel to wait until it's done before returning continuing with the macro. I'm not sure if there's a better way to do this that doesn't involve SendKeys, unless you want to paste into multiple cells and then combine them.