Macro Error - Microsoft Word - Run-time error '1004': Paste method of Worksheet class failed

Question

I am trying to run a script that searches for a keyword, then copies the entire sentence where that keyword is found, and pastes it on an excel spreadsheet.

When I run the script on a document that is 1-2 pages, it runs fine, but when I am try a much longer document (100+ pages), I get the following error:

Run-time error '1004': Paste method of Worksheet class failed. When I click "debug" it says "objsheet.paste" is the problem.

Could you please help me fix the code so that it can work with longer text, too?

Sub FindWordCopySentence()
    Dim appExcel As Object
    Dim objSheet As Object
    Dim aRange As Range
    Dim intRowCount As Integer
    intRowCount = 1
    Set aRange = ActiveDocument.Range
    With aRange.Find
        Do
            .Text = "Hair"
            .Execute
            If .Found Then
                aRange.Expand Unit:=wdSentence
                aRange.Copy
                aRange.Collapse wdCollapseEnd
                If objSheet Is Nothing Then
                    Set appExcel = CreateObject("Excel.Application")
                                         Set objSheet = appExcel.workbooks.Open("C:\Users\HNR\Desktop\hair.xlsx").Sheets("Sheet1")
                    intRowCount = 1
                End If
                objSheet.Cells(intRowCount, 1).Select
                objSheet.Paste
                intRowCount = intRowCount + 1
            End If
        Loop While .Found
    End With
    If Not objSheet Is Nothing Then
        appExcel.workbooks(1).Close True
        appExcel.Quit
        Set objSheet = Nothing
        Set appExcel = Nothing
    End If
    Set aRange = Nothing
End Sub

Sorry, I tried pasting the code and it was all jumbled into a paragraph. I didn't know how to fix it (new to forum and just learned what 'macros' is today to try this code). I don't know how to post the code in the right format. :\ But I edited the original post with the jumbled text (and a link to the original code, if you click it and scroll down it's there in the right format). — IrisRose
Once you get the code into the question you need to indent every line by 4 spaces to make it look good. Ctrl-K is a shortcut key to do that. — YowE3K
FWIW - my guess that opening the workbook was causing the clipboard to be cleared wasn't correct. — YowE3K
Thank you for fixing my original message. The code is now there :) — IrisRose
I'm not a Word-VBA expert (I came across your question while removing the incorrect "macros" tag) but until an actual expert comes along can you try changing aRange.Copy to myTempText = aRange.Text, and objSheet.Cells(intRowCount, 1).Select objSheet.Paste to objSheet.Cells(intRowCount, 1).Value = myTempText - that might help determine whether it is a copy/paste related problem or something else. (I can't replicate the problem with some dummy data that I set up, so I'm thinking there might be something in your document itself which is causing problems with copy/paste) — YowE3K

YowE3K YowE3K · Accepted Answer · 2017-04-12T20:30:46

If the issue is due to copy/pasting the information, that can be avoided by just assigning the text directly:

Sub FindWordCopySentence()
    Dim appExcel As Object
    Dim objSheet As Object
    Dim aRange As Range
    Dim intRowCount As Integer
    Dim myTempText As String
    intRowCount = 1
    Set aRange = ActiveDocument.Range
    With aRange.Find
        Do
            .Text = "Hair"
            .Execute
            If .Found Then
                aRange.Expand Unit:=wdSentence
                'Store the text into a variable
                myTempText = aRange.Text
                aRange.Collapse wdCollapseEnd
                If objSheet Is Nothing Then
                    Set appExcel = CreateObject("Excel.Application")
                    Set objSheet = appExcel.workbooks.Open("C:\Users\HNR\Desktop\hair.xlsx").Sheets("Sheet1")
                    intRowCount = 1
                End If
                'Set the destination cell to the text we stored
                objSheet.Cells(intRowCount, 1).Value = myTempText
                intRowCount = intRowCount + 1
            End If
        Loop While .Found
    End With
    If Not objSheet Is Nothing Then
        appExcel.workbooks(1).Close True
        appExcel.Quit
        Set objSheet = Nothing
        Set appExcel = Nothing
    End If
    Set aRange = Nothing
End Sub

Another potential cause of the issue is if you were bored while processing the large document, so you left it running in the background while doing other copy/paste operations on something else.

Copy and Paste share the clipboard with other applications so, if you do a copy between when the code did its Copy and when it did its Paste, it will be trying to Paste what you copied instead of what it copied.

So, whenever possible, avoid using Copy/Paste within code.

Macro Error - Microsoft Word - Run-time error '1004': Paste method of Worksheet class failed

1 Answers