Copy and paste from column without quotations on carriage return.


New Member
Jan 14, 2014
I have several textboxes with buttons that populate several cells in a single column and then copies that column so that I can then paste the contents into a seperate company data entry program that is web based. I have enabled the enter key (for carriage returns) in the textboxes so that paragraphs can be written. However, if the return button is used in a text box, it translates over to the assigned cell as if I had selected <alt enter>. Therefore, whenever I paste it into the web based program, annoying quotation marks show up around the data in that cell when the column is copied.

This can be duplicated by entering text into any cell and using the <alt enter> function at some point. If you copy and paste it into Microsoft Notepad, you will see the unwanted quotation marks I am talking about. I have found code that eliminates this for individual cells, but I cannot figure out how to adapt it for the entire column. I have attached the macro I found below.

The issue is in line 4 where it uses 'ActiveCell.Text'. This only allows the text for the first cell in the column to be copied. I need the entire column. I have tried many variations without success. I was wondering if any of you know the proper code to select the entire column. It is probably an easy solution but it has me scratching my nogin.

 Option Explicit
 Sub testme()

 Dim MyDataObj As DataObject
 Set MyDataObj = New DataObject

 MyDataObj.SetText ActiveCell.Text

End Sub

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.


New Member
Jan 14, 2014
I found another way to solve this issue by running the column through Word first. I am posting my solution in the event any of you find it useful. Thanks

Sub CopyToWord()
  Dim objWord As Object
    Set objWord = CreateObject("Word.Application")
  'Copy the range Which you want to paste in a New Word Document
    With objWord
        .Visible = False
        .Quit SaveChanges:=False
    End With

End Sub

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics

  • Copy entire row if CountA <>0 to another sheet
    [B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
  • Select last used Row in Table
    I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
  • excel workbook: do not allow certain file name
    Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
  • fixing problem autofilter
    hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
  • “Weight”
    Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
  • How to capitalize everything before a certain character?
    In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...