I have sheet which is a template for a letter to be printed.

The letter can have a variable number of list-like lines it so using the
sheet once filled and printed is problematical so I delete the sheet to be
printed and copy a new one from another sheet which has the base image.


Here is the part of the code which fails.

' If "Printwork" sheet does not exist, create it
' if it does exist, delete it first before copy.
Pwork = False
Set HH = ActiveWorkbook.Sheets
For Each Sht In HH
If LCase(Sht.Name) = "printwork" Then
Pwork = True
End If
Next
Set HH = Nothing
If Pwork = True Then
Sheets("Printwork").Delete
End If
Application.DisplayAlerts = True

' Copy printing template as "Printwork"
Application.DisplayAlerts = False
'Sheets("RenewalTemplate").Select
Sheets("RenewalTemplate").Copy Befo=Sheets(1)

and just in case it is pertinent, here is the code which prints after the
copy and sheet changes are made.

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True,
ActivePrinter:=""

I forgot to add that the failure comes after 50 copy then print cycles.

"NHRunner" wrote in message
...
I have sheet which is a template for a letter to be printed.

The letter can have a variable number of list-like lines it so using the
sheet once filled and printed is problematical so I delete the sheet to be
printed and copy a new one from another sheet which has the base image.


Here is the part of the code which fails.

' If "Printwork" sheet does not exist, create it
' if it does exist, delete it first before copy.
Pwork = False
Set HH = ActiveWorkbook.Sheets
For Each Sht In HH
If LCase(Sht.Name) = "printwork" Then
Pwork = True
End If
Next
Set HH = Nothing
If Pwork = True Then
Sheets("Printwork").Delete
End If
Application.DisplayAlerts = True

' Copy printing template as "Printwork"
Application.DisplayAlerts = False
'Sheets("RenewalTemplate").Select
Sheets("RenewalTemplate").Copy Befo=Sheets(1)

and just in case it is pertinent, here is the code which prints after the
copy and sheet changes are made.

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True,
ActivePrinter:=""

I have seen errors occur when copy of sheets is performed many times,
somewhere between 50 and 250 iterations.
Only solution seems be a .Save and maybe a Close/Open.

NickHK

"NHRunner" wrote in message
...
I forgot to add that the failure comes after 50 copy then print cycles.

"NHRunner" wrote in message
...
I have sheet which is a template for a letter to be printed.

The letter can have a variable number of list-like lines it so using the
sheet once filled and printed is problematical so I delete the sheet to
be
printed and copy a new one from another sheet which has the base image.


Here is the part of the code which fails.

' If "Printwork" sheet does not exist, create it
' if it does exist, delete it first before copy.
Pwork = False
Set HH = ActiveWorkbook.Sheets
For Each Sht In HH
If LCase(Sht.Name) = "printwork" Then
Pwork = True
End If
Next
Set HH = Nothing
If Pwork = True Then
Sheets("Printwork").Delete
End If
Application.DisplayAlerts = True

' Copy printing template as "Printwork"
Application.DisplayAlerts = False
'Sheets("RenewalTemplate").Select
Sheets("RenewalTemplate").Copy Befo=Sheets(1)

and just in case it is pertinent, here is the code which prints after
the
copy and sheet changes are made.

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True,
ActivePrinter:=""

Thanks for the confirmation of the perceived issue.

A close/open would probably work but since I'm driving the copy/update/print
cycle from a job build memory array I would need to used a column in sheet
as the storage medium. Then drive the cycle by processing the rows and
clearing the cells that have caused a print. I hate a hack.

"NickHK" wrote in message
...
I have seen errors occur when copy of sheets is performed many times,
somewhere between 50 and 250 iterations.
Only solution seems be a .Save and maybe a Close/Open.

NickHK

"NHRunner" wrote in message
...
I forgot to add that the failure comes after 50 copy then print cycles.

"NHRunner" wrote in message
...
I have sheet which is a template for a letter to be printed.

The letter can have a variable number of list-like lines it so using
the
sheet once filled and printed is problematical so I delete the sheet to
be
printed and copy a new one from another sheet which has the base image.


Here is the part of the code which fails.

' If "Printwork" sheet does not exist, create it
' if it does exist, delete it first before copy.
Pwork = False
Set HH = ActiveWorkbook.Sheets
For Each Sht In HH
If LCase(Sht.Name) = "printwork" Then
Pwork = True
End If
Next
Set HH = Nothing
If Pwork = True Then
Sheets("Printwork").Delete
End If
Application.DisplayAlerts = True

' Copy printing template as "Printwork"
Application.DisplayAlerts = False
'Sheets("RenewalTemplate").Select
Sheets("RenewalTemplate").Copy Befo=Sheets(1)

and just in case it is pertinent, here is the code which prints after
the
copy and sheet changes are made.

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True,
ActivePrinter:=""