Results 1 to 2 of 2

Thread: Excel 2007 Macro runtime error '1004'

  1. #1
    Join Date
    Jan 2012
    Posts
    1

    Excel 2007 Macro runtime error '1004'

    I had created a macro in Excel 2007 by using the 'Record Macro' function. Up until recently, it worked just fine. Then, without any explanation (and separate from any recent Windows updates), it started hitting 'errors' and popping up the debugger. Upon reaching the limit of my understanding, I thought the best option would be to delete the original macro and re-record it from scratch.
    Now, I get -- "Run-time error '1004': | Application-defined or object-defined error". The vertical bar is simply representing the linebreak/carriage return.
    When I click on 'Debug', it highlights ".Color = -16752384" as part of formatting the font. The color was selected from a pre-defined format.

    What do I do?!?

    Let me know if I need to include the macro text.

  2. #2
    Join Date
    Dec 2007
    Posts
    2,291

    Re: Excel 2007 Macro runtime error '1004'

    There was a kb article or solution provided for the same kind of error resolution that you can follow below:

    To resolve this problem, save and close the workbook periodically while the copy process is occurring, as in the following sample code:
    Code:
    Sub CopySheetTest()
        Dim iTemp As Integer
        Dim oBook As Workbook
        Dim iCounter As Integer
        
        ' Create a new blank workbook:
        iTemp = Application.SheetsInNewWorkbook
        Application.SheetsInNewWorkbook = 1
        Set oBook = Application.Workbooks.Add
        Application.SheetsInNewWorkbook = iTemp
        
        ' Add a defined name to the workbook
        ' that RefersTo a range:
        oBook.Names.Add Name:="tempRange", _
            RefersTo:="=Sheet1!$A$1"
                
        ' Save the workbook:
        oBook.SaveAs "c:\test2.xls"
        
        ' Copy the sheet in a loop. Eventually,
        ' you get error 1004: Copy Method of
        ' Worksheet class failed.
        For iCounter = 1 To 275
            oBook.Worksheets(1).Copy After:=oBook.Worksheets(1)
            'Uncomment this code for the workaround:
            'Save, close, and reopen after every 100 iterations:
            If iCounter Mod 100 = 0 Then
                oBook.Close SaveChanges:=True
                Set oBook = Nothing
                Set oBook = Application.Workbooks.Open("c:\test2.xls")
            End If
        Next
    End Sub

Similar Threads

  1. Excel 2003 Macro doesn't work in Excel 2007
    By jjaw in forum Windows Software
    Replies: 3
    Last Post: 03-01-2014, 03:28 PM
  2. Excel 2007 macro help needed
    By AZUL in forum Software Development
    Replies: 4
    Last Post: 19-11-2009, 06:10 PM
  3. Set the Macro Security Level in Excel 2007
    By NIcaBoy in forum Windows Software
    Replies: 3
    Last Post: 21-04-2009, 02:04 PM
  4. Macro sum problem in excel 2007
    By Radley in forum Windows Software
    Replies: 2
    Last Post: 07-04-2009, 09:46 PM
  5. Runtime Error 1004 in vba excel
    By Viensterrr in forum Windows Software
    Replies: 2
    Last Post: 17-03-2009, 03:04 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Page generated in 1,711,715,522.44373 seconds with 17 queries