On Fri, 11 Aug 2006 00:23:48 +0100, "Bob Phillips"
wrote:

Richard,

Have you checked for MISSING references in the VBIDE (ToolsReferences)?
Clear down any you find.


Bob,

I've traced the cause to the following sub-section of code

If TempWb.Sheets.Count < 1 Then 'Deletes excess sheets if more than
one

For y = 1 To TempWb.Sheets.Count - 1
Worksheets(y).Delete
Next
End If

The Excel default for the number of sheets when a new workbook is
added, seems to be 3. I have this code mainly for presentation
purposes. I want to control the number of sheets added and don't want
two superfluous sheets in place when the macro has completed.

This code comes immediately after the Workbooks.Add and before the
PasteSpecial command.

When I comment it out, everything seems to work OK.

Any ideas why this may be causing problems?


In case it's of any help, the complete code up until the bit that
falls over is below
The mylist variable I'm passing is a range


Sub OutputFiles(mylist)
Dim x As Integer, y As Integer, z As Integer
Dim iRowNo As Integer
Dim First As Integer


Application.ScreenUpdating = False

Set MyWb = ActiveWorkbook
stShServRows =
MyWb.Worksheets("OHDTemplate").Range("shserv_rows" ).Address
Folder = Range("folder")
Application.DisplayAlerts = False
For x = 0 To mylist.Rows.Count - 1
If Menu.ListBox1.Selected(x) = True Then
Range("jobno") = mylist.Cells(x + 1, 1)
Filename = "OHD_" & Range("Jobno") & "_" &
Range("Periodno") & "_" & Range("yearno")
Range("template").Copy
stShName = Range("jobno")
stOHDTemplate = stShName
Workbooks.Add: Set TempWb = ActiveWorkbook
'ActiveSheet.Name = stShName

If TempWb.Sheets.Count < 1 Then 'Deletes excess sheets
if more than one
For y = 1 To TempWb.Sheets.Count - 1
Worksheets(y).Delete
Next
End If
ActiveSheet.Name = stShName

Range("A1").PasteSpecial (xlPasteValues):
Range("A1").PasteSpecial (xlPasteFormats)
Range("A1").PasteSpecial (xlPasteColumnWidths)

.....................
Regards
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________