Copy and other functions not stable. What is happening?

[Lima Mike]

Dear All,

In my payroll program I have an option to make a workbook full of payslips.
Some of the times this goes Ok but more often it fails. I don’t understand why.

The program first creates a new workbook by copying the sheet PAYSLIP MODEL to a new workbook.
If an old version is open then this is first closed by the program.
The new workbook is saved as ‘Paybook January 2006.xls’ (in certain circumstances this goes wrong see below)
In this new workbook the sheet ("PAYSLIP MODEL (2)").is copied for each new payslip and data from the payroll is transferred to it.
The program switches between the two spreadsheets all the time in order to load the data for each payslip. (by using “Windows(theThisFileName).Activate” and “Windows(theFileName).Activate”.

Usually the first try goes Ok but repeated use gets into more and more problems. (the first time I manage to make 248 sheets in a workbook but the second time only some 49 and then it cant even make one anymore and Excel crashes. Only rebooting the computer gets rid of the problems. (Cant even delete the old files created because they are “In use”)

What is happening?


Below a list of commands and their error messages:

Windows(theThisFileName).Activate
Run-time error ‘-2147417848 (80010108)
Method ‘ _Default’ of object ‘Windows? Failed

Cells.Columns.AutoFit
Run-time error ‘-2147417848 (80010108)’ :
Method ‘AutoFit? Of object ‘Range? Failed

Sheets("PAYSLIP MODEL (2)").Copy After:=Sheets(1)
Run-time error ‘1004’:
Copy method of Worksheet class failed


Sheets("PAYSLIP MODEL (2)").Copy After:=Sheets(1)
Run-time error ‘-2147417848 (80010108)’:
Automation error
The object invoked has disconnected from its clients.



EXCEL.EXE – Application Error
The instruction at “0x3005ba44” referenced memory at “0x00000004”. The memory could not be “read”.

Click on OK to terminate the program

This last one causes as well the excel program to start up as a read only file.
After that I get this one. (On a file that cant be deleted).

ActiveWorkbook.SaveAs Filename:=theSafe, FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
Run-time error ‘1004’:
Connot access ‘Paybook January 2006.xls’.

 

[Lima Mike]

Hi Norie,

You are right.
But the problem is in getting more sheets in the new workbook and the instability of the thing.

Lima Mike

 

[ringmaker]

Copying Sheets Error - try removing named ranges

I was having a similar problem when copying sheets multiple times. The macro would cycle through about 30 times (out of an intended 100 times) and then I would get the error "1004 copy method of worksheet class failed". I discovered that if I clear out the named ranges in the sheet I am copying, the macro successfully cycles through in its entirety.

To clear out the named ranges, I used a macro like the one below:

For Each n In ThisWorkbook.Names
n.Delete
Next n

When you copy a sheet from one workbook to another, all the range names from the first workbook tag along. In my case, I have lots of named ranges and they were causing the new workbook to become large and I suppose using up memory.

Hope this is helpful.