paste fails error 1004 in VBA Excel 2010

mikec · #1 05-05-2013, 05:07 AM

I have 3 Excel tables. Table 1 (SummerCompTable) includes all the records found in tables 2 (POYCompTable) and 3 (ScratchCompTable). Table 1 is used to edit the records and has a column that indicates where each record in table 1 belongs. On completion of editing I need to copy edit table records to table 2, 3 or to both.

To get started I created the following macro:

Code:

Sub Macro4()
Range("SummerCompTable").Select 'Editing table
Selection.Range("A1:C1").Select 'Cols to be copied
Selection.Copy
Range("POYCompTable").Select 'Table 2
Selection.ListObject.ListRows.Add (1) 'New line at top of table
Selection.Range("A1:C1").Select 'Tried Range("A1") but paste still fails
Application.ActiveSheet.Paste 'Fails with run time error 1004 - error message "Paste method of Worksheet class failed"
'Selection.PasteSpecial 'This doesn't work either
End Sub

What I can't understand is that if I stop the macro before the activesheet.paste line I can complete the paste manually from the clipboard.
Any suggestions?

macropod · #2 05-06-2013, 12:18 AM

Perhaps:

Code:

Sub Macro4()
Range("SummerCompTable").Range("A1:C1").Copy
With Range("POYCompTable")
  .ListObject.ListRows.Add (1)
  .Range("A1").PasteSpecial
End With
End Sub

PS: When posting code, please use the code tags. They're on the 'Go Advanced' tab.

mikec · #3 05-06-2013, 01:20 AM

I love the compactness of the code. I see I've still got a lot to learn. Unfortunately I get the same error form line .Range("A1").PasteSpecial
As before I can take the "Debug" option and successfully Paste using the Clipboard. However, I note that Ctrl + V does not work. The sheet is not protected and as no other code is involved I'm at a loss.

macropod · #4 05-06-2013, 04:20 AM

Can you attach a workbook to a post containing your tables with some representative data (delete anything sensitive)? You do this via the paperclip symbol on the 'Go Advanced' tab? If so, I can take more of a look at what's going on.

mikec · #5 05-06-2013, 05:50 AM

I just spent several minutes telling you about the project then seemed to hit the wrong button - black holed it.
Again Sheets 1, 2 and 3 list competitions for which players get points. The Diaries form lets the user edit the comp data. My current design has 3 tables with comp data but POY and Scratch share some competitions. Users have to double edit the dates and could cause some confusion by giving the same comp different names. So I decided to create a single table for POY and Scatch comps. Once the user comits to an update I decided to use the existing code under cmdPostData_Click (see also cmdPostData created from original _Click Sub). So my new code some in Module 4 some in PostDataClick and the problem area in Modul6 is intended to zap and rebuild the POYCompTable and ScratchCompTable and let the more or less unchanged PostData Sub run as before but with added parameters.

macropod · #6 05-06-2013, 04:12 PM

Try:

Code:

Sub Macro4()
    With Range("POYCompTable")
        .ListObject.ListRows.Add (1)
        Range("SummerCompTable").Range("A1:C1").Copy
        .ListObject.ListRows(1).Range.PasteSpecial
    End With
End Sub

mikec · #7 05-07-2013, 12:19 AM

Thanks for that; I would never have thought of putting a piece of code in the middle of a With loop but when you see it it's obvious enough. What I still do not understand is why the Copy has to be positioned between the Add and the Paste or should I say that the Paste has to be immediately after the Copy; (I tried it and it failed as before.)
If you have time to clarify I will go from very happy to ecstatic.

macropod · #8 05-07-2013, 12:34 AM

I'm no Excel expert but I suspect the issue is that the '.ListObject.ListRows.Add (1)' line clears the clipboard (which I'd describe as a bug), rather than there being a requirement to have copy & paste only one line apart.

mikec · #9 05-07-2013, 01:16 AM

I'm developing the code right now. In its present format the clipboard is not cleared by the Add. I will investigate some more once I've got the code running properly and let you know if I find a cause.
Thanks again

macropod · #10 05-07-2013, 01:19 AM

But in the current implementation, the clipboard is populated via the .Copy after the .Add!

mikec · #11 05-07-2013, 01:45 AM

yes but if the Add clears the clipboard the clipboard would only contain the new Copy. In fact it retains all the copies made as I loop through the source table or did I misunderstand your meaning?

macropod · #12 05-07-2013, 01:50 AM

OK, you've got me confused - I don't know what you mean by "all the copies made as I loop through the source table". The code discussed in this thread concerns inly a single instance. If you're calling the macro for each update or you've added the code to a loop, then naturally, each new row has its own add-copy-paste sequence, so the clipboard is being re-populated each time.

mikec · #13 05-07-2013, 02:23 AM

Sorry; my partly developed code is as below. It doesn't work yet because I have to develop separate loop counters for the two tables before calling the InserLine sub. What I can say is that the ListRows.Add line does not clear the clipboard of previous copy entries. I will have to go off line now but will be happy to continue the conversation later unless you've had enough!

Code:

Sub Macro1() 
    Dim lnN As Integer 
     'Dim lcWasAt As String
    Sheets("Diary").Activate 
    Range("SummerCompTable").Select 
    For lnN = 1 To Selection.ListObject.ListRows.Count 
        Range("SummerCompTable").Range(Cells(lnN, 4), Cells(lnN, 4)).Select 
        Select Case ActiveCell 
        Case "Both" 
            Call InsertLine("POYCompTable", lnN) 
            Call InsertLine("ScratchCompTable", lnN) 
        Case "POY" 
            Call InsertLine("POYCompTable", lnN) 
        Case "Scratch" 
            Call InsertLine("ScratchCompTable", lnN) 
        Case Else 
            MsgBox "Data error in 'Where' column" 
            Exit Sub 
        End Select 
    Next lnN 
End Sub 
  
Sub InsertLine(lcDiaryName As String, lnLoopNo As Integer) 
    With Range(lcDiaryName) 
        .ListObject.ListRows.Add (lnLoopNo) 
        Range("SummerCompTable").Range(Cells(lnLoopNo, 1), Cells(lnLoopNo, 3)).Copy 
        .ListObject.ListRows(lnLoopNo).Range.PasteSpecial 
    End With 
End Sub

macropod · #14 05-07-2013, 02:32 AM

As I suspected, your code is refreshing the clipboard contents on each pass...

PS: When posting code, please use the code tags. They're on the 'Go Advanced' tab.

mikec · #15 05-08-2013, 05:32 AM

I have now completed the code revisions and everything is working well. I have checked through again and can confirm that if the .Copy (table1 row) is placed before the .Add (table2 row) that the buffer is cleared causing the .PasteSpecial to fail. As you said it would seem this is a bug as there is no logical reason for such an action. Thanks again for your help.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Paste special an Excel range into Outlook as an Excel Worksheet	charlesh3	Excel Programming	3	02-04-2013 04:33 PM
Outlook 2010 & WM 6.5 - recurring appt fails to sync	ghumdinger	Outlook	1	08-11-2012 02:20 PM
Run time error 1004	yonasan	Excel Programming	3	06-12-2012 11:08 PM
runtime error 1004	gbaker	Excel Programming	11	06-06-2012 05:23 AM
Microsoft office 2010 error 2908 and error 1935 !!!!!!heeeeellpppp!!!!!!!!!	bennypryde	Office	1	01-05-2012 03:33 PM