Macro - Copy Text Only

[Dave Punky]

Hi all,

Just a quick one (hopefully) but is there any way to copy just the text in a selection. For example I have range A1:A5 with text and want to create a macro to copy this, but I don't want it to create a table where I'm pasting it - literally just copy the text as is.

I'm sure I've seen this somewhere but I can't seem to find it anywhere.

 

[hiker95]

Dave Punky,

What version of Excel are you using?

You will generally get much more help (and faster) in this forum if you can post your small samples directly in the forum.

Two reasons:
- helpers don't have to spend extra time tracking to other sites to find your samples.
- you can post samples here where helpers can copy your sample data to their own test worksheets. Most of us don't like manually typing sample data!

Here are three possible ways to post small (copyable) screen shots directly in your post:

Please post a screenshot of your sheet(s), what you have and what you expect to achieve, with Excel Jeanie HTML 4 (contains graphic instructions).
http://www.excel-jeanie-html.de/html/hlp_schnell_en.php

or
RichardSchollar’s beta HTML Maker -...his signature block at the bottom of his post

or
Borders-Copy-Paste

 

[Dave Punky]

Hi Hiker,

I thought my description would have been self explanitory but none the less. I'm using Excel 2007 but this is going to into a Worksheet compatable with 97-2003.

This is what I want to do, this is the information in excel.

Or if the image doesn't work the link:
http://img638.imageshack.us/i/excel1.gif/

I want it to copy into any program (ie Word) without the formatting around it for tables, literally just the plain text.

Or if the image doesn't work the link:
http://img213.imageshack.us/i/excel2.gif/

 

[hiker95]

Dave Punky,

I am not able to assist you.

Click on the Post Reply button, then just enter BUMP, and then click on the Submit Reply button.

Someone else will assist you.

 

[Dave Punky]

Bump

 

[twistinginthewind]

It's not an issue with copying... but an issue with pasting.

In word select Paste Special, then in the box that appears select "Unformated Text"

This should work for you.

If you want to change the way the data actually copies, I only know how to do that with a macro.

 

[Dave Punky]

I'm not copying it into Word though, I'm copying it into a webform which doesn't have paste special as an option.

And I want a macro which changes the way it copies into just the text. All I want it to do is copy to the clipboard as text only, instead of with the table formatting. I'm certain I have seen this done without issue in the past though...

 

[Jaafar Tribak]

I'm not copying it into Word though, I'm copying it into a webform which doesn't have paste special as an option.

And I want a macro which changes the way it copies into just the text. All I want it to do is copy to the clipboard as text only, instead of with the table formatting. I'm certain I have seen this done without issue in the past though...

Are you copying and pasting via code or manually ?

 

[mvptomlinson]

This may work for you. The value of "str" at the end should be each cell's text followed by a carriage return/line feed. I haven't tested it, but it should paste elsewhere without any table-like formatting.

Sub copyText()
Dim ce As Range, str As String
For Each ce In Range("A1:A5")
    str = str & ce.Value & vbCrLf
Next ce
End Sub

 

[Jaafar Tribak]

See if this Sub Routine works for you: ( Place in a Standard module )

Option Explicit

Private Declare Function OpenClipboard Lib "user32.dll" _
(ByVal hwnd As Long) As Long
 
Private Declare Function CloseClipboard Lib _
"user32.dll" () As Long
 
Private Declare Function EmptyClipboard Lib _
"user32.dll" () As Long
 
Private Declare Function SetClipboardData Lib _
"user32.dll" _
(ByVal wFormat As Long, _
ByVal lMem As Long) As Long
 
Private Declare Function GlobalAlloc _
Lib "kernel32" _
(ByVal uFlags As Long, _
ByVal dwBytes As Long) As Long

Private Declare Function GlobalLock _
Lib "kernel32" _
(ByVal lMem As Long) As Long
    
Private Declare Function GlobalUnlock _
Lib "kernel32" _
(ByVal lMem As Long) As Long
    
Private Declare Sub MoveMemory _
Lib "kernel32" Alias "RtlMoveMemory" _
(ByVal strDest As Any, _
ByVal lpSource As Any, _
ByVal Length As Long)


Private Const GMEM_MOVABLE = &H2&
Private Const GMEM_DDESHARE = &H2000&
Private Const CF_TEXT = 1


Private Sub CopyRangeText(Rg As Range)

    Dim sText As String
    Dim lMem As Long
    Dim lSize As Long
    Dim lpMem As Long
    Dim cl As Long
    Dim rw As Long

    With Rg
        For rw = 1 To .Rows.Count
            For cl = 1 To .Columns.Count
                sText = sText & CStr(.Cells(rw, cl)) & vbTab
            Next cl
            sText = sText & vbVerticalTab
        Next rw
    End With
    
    lSize = Len(sText) + 1
    lMem = GlobalAlloc(GMEM_MOVABLE Or _
    GMEM_DDESHARE, lSize)
    If lMem <> 0 Then
        lpMem = GlobalLock(lMem)
        If lpMem <> 0 Then
            Call MoveMemory(lpMem, sText, lSize)
            Call GlobalUnlock(lMem)
            OpenClipboard 0
            EmptyClipboard
            SetClipboardData 1, lMem
            CloseClipboard
        End If
    End If
    
End Sub

Usage example : ( This will copy the text in Range A1:F10 to the clipboard without any formatting )

Sub Test()
    Call CopyRangeText(Range("A1:F10"))
End Sub