none
Run time error '1004' method pastespecial of object _worksheet failed

    Question

  •  Sub Worksheet_Change(ByVal Target As Range, ByVal sht As Worksheet)

        Dim objData As DataObject ' Set a reference to MS Forms 2.0
        Dim sHTML As String
        Dim sSelAdd As String
        Application.EnableEvents = False
        If Target.Cells.Count = 1 Then
                Set objData = New DataObject
                sHTML = Target.Text
                objData.SetText sHTML
                objData.PutInClipboard
                Target.Select
                sht.PasteSpecial Format:="Unicode Text"
                ActiveCell.WrapText = True
                ActiveCell.VerticalAlignment = xlTop
                End If
         Application.EnableEvents = True
    End Sub

    Got Error Msg:--

    Run time error '1004'

    method pastespecial of object _worksheet failed

    Monday, December 09, 2013 11:38 AM

Answers

  • Hi CoolAtual,

    I test the code on Windows 8 + Office 2013 and Windows 7 + Office 2010. When I open the Excel file, it prompts a dialog like fingure below:

    After close the dialog, I clicked the button "Show Redlined Text". The code works like a charm both in Excel 2010 and Excel 2013. I suggest you start the Excel in safe mode to diable other add-ins for Excel then click the button to see whether this issue was fixed. You can Press and hold the CTRL key, and then click Excel to run in safe mode and it will prompt the macro is diabled in the message bar please enable it befor you click the button.

    Best regards

    Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by CoolAtul Friday, January 03, 2014 5:28 AM
    Tuesday, December 24, 2013 3:28 AM
    Moderator

All replies

  • Hello CoolAtul,

    Please try to use the following line of code:

     sht.PasteSpecial Format:="HTML"

    Monday, December 09, 2013 2:04 PM
  • its not working is still giving the same error in Excel 2010
    Tuesday, December 10, 2013 4:31 AM
  • Hi CoolAtul,

    There is only one parameter in Worksheet.Change Event (Excel).

    Please try to use ActiveSheet instead of sht parameter.

    Private Sub Worksheet_Change(ByVal Target As Range)
       Set sht = ActiveSheet
       Dim objData As DataObject
       Set objData = New DataObject
       
       Application.EnableEvents = False
       objData.SetText Target.Text
       objData.PutInClipboard
       Target.Select
       sht.PasteSpecial Format:="Unicode Text"
       Application.EnableEvents = True
    End Sub
    


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Tuesday, December 10, 2013 9:36 AM
    Moderator
  • Hi Jeffrey,

    I am still facing this problem ...Actually in a cell if a text is too long then this error occurs.....

    My Application is of Patent Domain So the Text is too Long in each Cell please suggest me any other 

    fixes if possible.....

    Tuesday, December 17, 2013 9:57 AM
  • Hi,

    How long the text you type when the error occurs? The code ran well when I typed 16011 characters even more.

    Would you mind sharing with us the the sample file through skydrive to troubleshoot this issue?

    You can remove the sessitive information and keep it as simple as possible.

    Best regards

    Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Wednesday, December 18, 2013 1:25 PM
    Moderator
  • Hi Fei,

    I have uploaded the Excel Sample File in which I am getting error .....

    My URL is - https://skydrive.live.com/?cid=9d9ea3eb7f23f85e&id=9D9EA3EB7F23F85E!105

    download this file & then open it,after open the file click on the Button "Show Redlined Text"

    now the cell vales are started converting in to HTML formatted Text, during conversion you will get an error--> "Run time error '1004' method pastespecial of object _worksheet failed"

    I am working on Excel 2010 Operating systema are Windows XP , Windows 7......

    your suggestion will be helpful Thanks in advance

    Best Regards

    Thursday, December 19, 2013 7:25 AM
  • Hi CoolAtual,

    I test the code on Windows 8 + Office 2013 and Windows 7 + Office 2010. When I open the Excel file, it prompts a dialog like fingure below:

    After close the dialog, I clicked the button "Show Redlined Text". The code works like a charm both in Excel 2010 and Excel 2013. I suggest you start the Excel in safe mode to diable other add-ins for Excel then click the button to see whether this issue was fixed. You can Press and hold the CTRL key, and then click Excel to run in safe mode and it will prompt the macro is diabled in the message bar please enable it befor you click the button.

    Best regards

    Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by CoolAtul Friday, January 03, 2014 5:28 AM
    Tuesday, December 24, 2013 3:28 AM
    Moderator