paste fails error 1004 in VBA Excel 2010

macropod · #16 05-08-2013, 05:51 AM

Hint: Your 'Macro1' sub could be reduced to:

Code:

Sub Macro1()
Application.ScreenUpdating = False
Dim lnN As Integer
With Range("SummerCompTable")
    For lnN = 1 To .ListObject.ListRows.Count
        Select Case .Cells(lnN, 4).Value
        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 With
Application.ScreenUpdating = True
End Sub

The essential logic difference between your code and the code I've posted here and previously is that nothing gets selected. Apart from the fact it means the worksheet doesn't even need to be activated (and, hence, your active cell need never change) when you run the code, working with range objects directly is much faster. Disabling screen updating while the code is running nets even greater performance increases.

mikec · #17 05-08-2013, 06:42 AM

Thanks again. My previous experience has been almost entirely on database management (FoxPro) and I still tend to think in terms of directly accessing tables to edit data. I also find this particular brand of OOP a bit hard to get used to largely because I find the MS Help documentation to unhelpful to be polite abut it. As an example I had not grasped the concept of Range being both an object and a property. Can you recommend any texts that deal with MS Office OOP, preferably aimed at those of us who are not quite computer professionals.

macropod · #18 05-08-2013, 03:11 PM

Hi Mike,

I can't really recommend anything on vba programming, as much of what I've learnt has been picked up from looking at how those who have gone before me have done things. These days I know enough to contribute the occasional gem of my own, but that's not very often - especially in Excel where there are many more proficient coders than me around.

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