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

 

[Damon Ostrander]

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]

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]

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]

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]

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

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

you could use

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]

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]

Mike

Can you post the rest of your code?

 

[Lima Mike]

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]

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.

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.

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.