Copy and other functions not stable. What is happening?

Lima Mike

Board Regular
Joined
Jun 3, 2003
Messages
70
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

Board Regular
Joined
Jun 3, 2003
Messages
70
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
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

ringmaker

Board Regular
Joined
Oct 26, 2003
Messages
75
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.
 

Forum statistics

Threads
1,141,299
Messages
5,705,571
Members
421,399
Latest member
hjweiss00

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top