Macro to copy the contents of the cell, but not the cell itself?

[swolfe2]

Hello all!

I currently have a userform that places a large amount of data into a cell. I have tried using the code below to copy that text so the end user can paste it into our documentation program.

Sub writeData()
Dim lastRow As Integer
With Sheets("Formatted Answers")
lastRow = .Cells(.Rows.count, "A").End(xlUp).Row + 1
'insert date cell
.Range("A" & lastRow).Value = Format(Now, "MM/DD/YY hh:mm:ss AM/PM")
'insert type:
.Range("B" & lastRow).Value = .Range("B3") & "-" & .Range("B4")
'Insert date
.Range("C" & lastRow).Value = formattedAnswers
'Sort by dates:
.Range("A6:C" & lastRow).Sort Key1:=.Range("A6:A" & lastRow), order1:=xlDescending, Header:=xlNo
.Range("A6:C" & lastRow).Rows.AutoFit
End With
Range("C6").Select
Selection.Copy
End Sub

However, because the code selects the entire cell, it applies quotation marks around the entire body once it is pasted into our program. This is causing errors in our reporting, and they need to be removed. If the user double clicks inside the cell, and manually selects the text, it doesn't apply the quotes.

Is there a way to have the macro automatically do this? All I really need is to copy the text in cell C6, since that's where the new data is placed each time.

Thanks for the help, as always!

[111StepsAhead]

The code

Range("C6").Select
Selection.Copy

works fine with notepad and microsoft word on my machine.

Maybe try the code

Range("C6").Copy

Instead

[swolfe2]

Hello 111!

Thanks for the quick reply!

I tried the change you suggested, but I got the same result. Like you, I tried pasting into a multitude of programs. However, SAP is the only one that has the quotes apply when the cell is copied.

What I'm looking for, I guess, is a macro that will select the text inside the cell without selecting the cell box.

Thanks!

[111StepsAhead]

swolfe2,

Are you able to copy-paste information from notepad, or some different program besides excel, to your program without the quotes? I'd like to rule out that your program is causing the issue.

[swolfe2]

Yes. I can paste from Word, websites, and any information outside of SAP. I can even paste from Excel, if I double click inside a cell and manually highlight items.

[111StepsAhead]

swolfe2,

I must go now but I believe the following will work for you.

Sub myTest()
    Range("C6").Select
    Selection.Copy
    Shell "notepad.exe", vbNormalFocus
    SendKeys "^v", True
    SendKeys "^a", True
    SendKeys "^c", True
    Application.SendKeys "%{F4}"
    Application.SendKeys "{TAB}"
    Application.SendKeys "{ENTER}"
End Sub

It copies the value in "C6", pastes it to an instance of notepad, copies the text from notepad, and closes notepad. The results is your value is on the "clipboard" for use. Anyways, good luck!

[swolfe2]

111,

Thanks for the help in getting started.
The code that you gave me is still applying the quotes around it, and pasting it into Notepad.

[swolfe2]

So, I figured it out!

Sub writeData()
Dim lastRow As Integer
Dim oData As DataObject
Set oData = New DataObject
Dim sClipBoard As String

With Sheets("Formatted Answers")
lastRow = .Cells(.Rows.count, "A").End(xlUp).Row + 1
'insert date cell
.Range("A" & lastRow).Value = Format(Now, "MM/DD/YY hh:mm:ss AM/PM")
'insert type:
.Range("B" & lastRow).Value = .Range("B3") & "-" & .Range("B4")
'Insert date
.Range("C" & lastRow).Value = formattedAnswers
'Sort by dates:
.Range("A6:C" & lastRow).Sort Key1:=.Range("A6:A" & lastRow), order1:=xlDescending, Header:=xlNo
.Range("A6:C" & lastRow).Rows.AutoFit
End With
' Clears the clipboard
oData.SetText ""
oData.PutInClipboard

' Puts the text from a cell into the clipboard
sClipBoard = Worksheets("Formatted Answers").Range("C6").Text
oData.SetText sClipBoard


End Sub

Hopefully, that can help someone in the future that had the same problem as I did.

Thanks!