Nokia C3: Excel 2007 Macro runtime error '1004'

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.
Reply With Quote
#2
Old 13-01-2012
EINSTEIN_007's Avatar
EINSTEIN_007 EINSTEIN_007 is offline
Member

Join Date: Dec 2007
Posts: 2,025
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

__________________
Education, Career and Job Discussions