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’.
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Damon Ostrander

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
Hi Lima Mike,

I'm wondering if the problem has to do with where the code resides. If it resides in an event code module (esp. a sheet or ThisWorkbook module) it may be a problem with conflicting or ambiguous qualifications of worksheet ranges. In this case you should move the body of the code to standard macro modules. You can still use the worksheet or ThisWorkbook events, but they should call the code (in the form of Sub procedures) in the standard macro modules.

Damon
 

Lima Mike

Board Regular
Joined
Jun 3, 2003
Messages
70
Hi Damon,

The code is in a normal module.

I noticed that all works fine in the first run. (250 sheets in new workbook)
In the second in crashes at 49 sheets
and the tird time at 1.
and then Excel crashes.

Lima Mike
 

Damon Ostrander

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
Hi again Lima Mike,

In that case, my bet would be that it has something to do with garbage collection associated with all those window activates/deactivates. Have you thought of eliminating them? Even if it doesn't solve the crashing problem it should make the program run much quicker if you just refer to each workbook using an object variable and using it to qualify all references to objects in that workbook.

Damon
 

Lima Mike

Board Regular
Joined
Jun 3, 2003
Messages
70

ADVERTISEMENT

Hi Damon,

Not sure what you mean.

Seeing the worsening when running again and again this macro you might indeed expect some garbage, but how to eliminate this?

Can you give me an example of the second point you mention about the references to objects?

Thanks,

Lima Mike
 

Damon Ostrander

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
Hi again Lima Mike,

As an example of what I was referring to, you mentioned using

Windows(theFileName).Activate

While this make it convenient to refer to objects in the theFileName workbook since objects in the active workbook don't need qualification, it also forces the window to be popped to the front and refreshed. With proper qualification, it is possible to refer to objects in theFileName workbook in the background without it being active and thus without the extra window management overhead. So instead of

Code:
Windows(theFileName).Activate
Sheets("Data1").Range("B4") = 25
Sheets("Data2").Range("A2") = "Updated"

you could use

Code:
With Workbooks(theFileName)
   .Sheets("Data1").Range("B4") = 25
   .Sheets("Data2").Range("A2") = "Updated"
End With

in which the workbook object is used to qualify the sheet references. Note also that this refers to the workbook object, not the window object.

I hope this helps.

Damon
 

Lima Mike

Board Regular
Joined
Jun 3, 2003
Messages
70

ADVERTISEMENT

Hi Damon,

Working on this I got the following code:

With Workbooks(theFileName)
.Sheets("PAYSLIP MODEL (2)").Copy After:=Sheets(1)
.Sheets("PAYSLIP MODEL (2)").Range("c1") = 25

PAYSLIP MODEL (2) has been created in the new workbook (theFileName) but the second line in the code copies this sheet back to my original workbook while I need a copy of it in the new workbook.
The third line writes 25 into the new workbook as requested.

How do I get a copy in the new workbook instead of the old one?

How do I get the data into the new sheet in the new workbook?
Do I have to define the name of the sheet each time (Here Sheets("PAYSLIP MODEL (2)"). But this would have a different name each time)
Or can I use With – End with again ? Like:
With Sheet (?????????)
. range (“C1”) = 25
. range (“C2”) = 21111
. range (“C3”) = 21313
end with

Lima Mike
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
Mike

Can you post the rest of your code?
 

Lima Mike

Board Regular
Joined
Jun 3, 2003
Messages
70
This is the full macro.

Private Sub PAYBOOK()
Application.ScreenUpdating = False
Dim theName As String
With UserFormLM3payroll
Dim theThisFileName As String
theThisFileName = ActiveWorkbook.Name
Dim theFilePath As String
theFilePath = ActiveWorkbook.Path
Dim theFileName As String
theFileName = "PAYBOOK " & Range("MONTH") & " " & Range("YEAR") & ".xls"
On Error GoTo theEH_Resume1 ' To close workbook if an old copy is still open.
Windows(theFileName).Activate
Application.DisplayAlerts = False
ActiveWorkbook.Close
theEH_Resume1:
On Error GoTo theEH_Message
Sheets("PAYSLIP MODEL").Visible = True
Sheets("PAYSLIP MODEL").Select
Sheets("PAYSLIP MODEL").Copy Before:=Sheets(1)
Columns("C:C").Select
Selection.ClearContents
Columns("A:A").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Range("C1").Select
Application.CutCopyMode = False
Sheets("PAYSLIP MODEL (2)").Move
Dim theSafe As String
theSafe = theFilePath & "\" & theFileName
ActiveWorkbook.SaveAs Filename:=theSafe, FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
Windows(theThisFileName).Activate

Dim theCounter As Integer
Dim thePL01 As String
Dim thePL02 As String
Dim thePL03 As String
Dim thePL04 As String
Dim thePL05 As String
Dim thePL06 As String
Dim thePL07 As String
Dim thePL08 As String
Dim thePL09 As String
Dim thePL10 As String
Dim thePL11 As String
Dim thePL12 As String
Dim thePL13 As String
Dim thePL14 As String
Dim thePL15 As String
Dim thePL16 As String
Dim thePL17 As String
Dim thePL18 As String
Dim thePL19 As String
Dim thePL20 As String
Dim thePL21 As String
Dim thePL22 As String
Dim thePL23 As String
Dim thePL24 As String
Dim thePL25 As String
Dim thePL26 As String
Dim thePL27 As String
Dim thePL28 As String
Dim thePL29 As String
Dim thePL30 As String
Dim thePL31 As String
Dim thePL32 As String
Dim thePL33 As String
Dim thePL34 As String
Dim thePL35 As String
Dim thePL36 As String
Dim thePL37 As String
Dim thePL38 As String
Dim thePL39 As String
Dim thePL40 As String
Dim thePL41 As String
Dim thePL42 As String
Dim thePL43 As String
Dim thePL44 As String
Dim thePL45 As String
Dim thePL46 As String
Dim thePL47 As String
Dim thePL48 As String
Dim thePL49 As String
Dim thePL50 As String

theCounter = 1
.SpinButtonStaff.Value = 9

theTryAgain:
Do Until .SpinButtonStaff.Value = .SpinButtonStaff.Max
.SpinButtonStaff.Value = .SpinButtonStaff.Value + 1
Run "FILL_PAYSLIP_DATA"
If .LR21 = Empty Then
If .SpinButtonStaff.Value = .SpinButtonStaff.Max Then GoTo theEnd
GoTo theTryAgain
End If

theCounter = theCounter + 1
If theCounter = 250 Then GoTo theEnd ' I seem to have a problem above 250. Do not know how to handle this.

Sheets("PAYSLIP MODEL").Select
thePL01 = Range("C1")
thePL02 = Range("C2")
thePL03 = Range("C3")
thePL04 = Range("C4")
thePL05 = Range("C5")
thePL06 = Range("C6")
thePL07 = Range("C7")
thePL08 = Range("C8")
thePL09 = Range("C9")
thePL10 = Range("C10")
thePL11 = Range("C11")
thePL12 = Range("C12")
thePL13 = Range("C13")
thePL14 = Range("C14")
thePL15 = Range("C15")
thePL16 = Range("C16")
thePL17 = Range("C17")
thePL18 = Range("C18")
thePL19 = Range("C19")
thePL20 = Range("C20")
thePL21 = Range("C21")
thePL22 = Range("C22")
thePL23 = Range("C23")
thePL24 = Range("C24")
thePL25 = Range("C25")
thePL26 = Range("C26")
thePL27 = Range("C27")
thePL28 = Range("C28")
thePL29 = Range("C29")
thePL30 = Range("C30")
thePL31 = Range("C31")
thePL32 = Range("C32")
thePL33 = Range("C33")
thePL34 = Range("C34")
thePL35 = Range("C35")
thePL36 = Range("C36")
thePL37 = Range("C37")
thePL38 = Range("C38")
thePL39 = Range("C39")
thePL40 = Range("C40")
thePL41 = Range("C41")
thePL42 = Range("C42")
thePL43 = Range("C43")
thePL44 = Range("C44")
thePL45 = Range("C45")
thePL46 = Range("C46")
thePL47 = Range("C47")
thePL48 = Range("C48")
thePL49 = Range("C49")
thePL50 = Range("C50")

theName = Left(Range("OUTPUT_PAYSLIP_01"), 30)

With Workbooks(theFileName)
.Sheets("PAYSLIP MODEL (2)").Copy After:=Sheets(1) 'This copies the sheet back to the original workbook.
.Sheets("PAYSLIP MODEL (2)").Range("c1") = 25 ' This works in the new workbook as planned but should be in the new sheet created there.

.Range("C1") = thePL01 'this does not work.
.Range("C2") = thePL02
.Range("C3") = thePL03
.Range("C4") = thePL04
.Range("C5") = thePL05
.Range("C6") = thePL06
.Range("C7") = thePL07
.Range("C8") = thePL08
.Range("C9") = thePL09
.Range("C10") = thePL10
.Range("C11") = thePL11
.Range("C12") = thePL12
.Range("C13") = thePL13
.Range("C14") = thePL14
.Range("C15") = thePL15
.Range("C16") = thePL16
.Range("C17") = thePL17
.Range("C18") = thePL18
.Range("C19") = thePL19
.Range("C20") = thePL20
.Range("C21") = thePL21
.Range("C22") = thePL22
.Range("C23") = thePL23
.Range("C24") = thePL24
.Range("C25") = thePL25
.Range("C26") = thePL26
.Range("C27") = thePL27
.Range("C28") = thePL28
.Range("C29") = thePL29
.Range("C30") = thePL30
.Range("C31") = thePL31
.Range("C32") = thePL32
.Range("C33") = thePL33
.Range("C34") = thePL34
.Range("C35") = thePL35
.Range("C36") = thePL36
.Range("C37") = thePL37
.Range("C38") = thePL38
.Range("C39") = thePL39
.Range("C40") = thePL40
.Range("C41") = thePL41
.Range("C42") = thePL42
.Range("C43") = thePL43
.Range("C44") = thePL44
.Range("C45") = thePL45
.Range("C46") = thePL46
.Range("C47") = thePL47
.Range("C48") = thePL48
.Range("C49") = thePL49
.Range("C50") = thePL50

.Cells.Columns.AutoFit
.Range("A1").Select
.Application.CutCopyMode = False
.ActiveSheet.Name = theName
End With
Loop
theEnd:
Windows(theFileName).Activate
ActiveWorkbook.Save
Exit Sub
theEH_Message:
MsgBox " An unknown error has occured! ", vbCritical, " Lemon3s software™."
End With
End Sub
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
Mike

I'm afraid I don't have time to thoroughly study your code at the moment.

But just having a quick glance I'm pretty sure it can be cleaned up/shortened somewhat.:)

I'll try and have a closer look later.

One thing I'm pretty sure of is that you could probably dispense with your 50 thePLX variables, perhaps using an array.
Code:
Dim thePL(1 To 50)
For I = 1 To 50
    thePL(I) = Sheets("PAYSLIP MODEL").Range("C" & I)
Next I
In fact you could probably dispense with the array and just do a straight copy.
Code:
Worksheets("PAYSLIP MODEL").Range("C1:C50").Copy Worksheets("New Sheet").Range("C1")
Please note both of these pieces of code are just ideas, like I said I'll take a closer look at the code later.:)
 

Forum statistics

Threads
1,136,928
Messages
5,678,623
Members
419,776
Latest member
mikelowski

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