Macro to Paste Text only

**Crawfy** · 12-12-2017, 01:11 AM

I have a protected workbook with various cells unlocked for editing.
I would like to be able to add a macro to enable me to paste just text only into these cells when i choose to.
I have used the VBA code below but it does not work all the time.

Sub PasteText()
    ActiveSheet.PasteSpecial Format:="Text", Link:=False, _
        DisplayAsIcon:=False
    
End Sub

If i select text from outside the workbook then the text will be pasted.
If i copy text from cells within the workbook i receive a Runtime error '1004':
PasteSpecial method of Worksheet class failed

Any ideas?

**mjr veverka** · 12-12-2017, 02:13 PM

This 'PasteSpecial' method is the method as it applies to the 'Worksheet' object, not to the 'Range' object. 'Cells' are 'Range' type objects.
There is a different syntax for this, e.g.:

    expression.PasteSpecial(Paste, Operation, SkipBlanks, Transpose)

**davesexcel** · 12-12-2017, 03:02 PM

Attaching a sample workbook enables others to work on your problem:

To attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

Click on GO ADVANCED and click "manage attachments" to open the upload window.

To add a file to a post

Macro to Paste Text only

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Macro to Paste Text only

Re: Macro to Paste Text only

Re: Macro to Paste Text only

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Macro to Copy and Paste Specific Text in Column A on the last line with Text in Column B?

[SOLVED] how to extract letters from text and paste them in new row with macro?

[SOLVED] Macro to search for text and paste data in other area

Macro or VBA code to open many files, copy text, close file, paste text in second workbook

Looping a macro to check text and then copy and paste

Macro Find Text, Copy & Paste Special

Macro to Cut-And-Paste Text

Bookmarks

Bookmarks

Posting Permissions