Richard,

It is the delete that is clearing the clipboard, making the pastespecial
fail.

When you add a workbook, the number of sheets is governed by the setting in
ToolsOptionsGeneral. You can circumvent this by telling VBA what type of
workbook to create, such as this

Workbooks.Add template:=xlWBATWorksheet

which always creates a single sheet workbook. It does name it Sheet1, 2 etc
instead of Book1, 2 , etc.

Another way is to set the SheetsInNewWorkbook property, like so

Application.SheetsInNewWorkbook = 1
Workbooks.Add

The problem with this is that it changes the application, so all new
workbooks now have 1 sheet, so probably best to save the value and
re-instate it

cSheets = Application.SheetsInNewWorkbook
Application.SheetsInNewWorkbook = 1
Workbooks.Add
Application.SheetsInNewWorkbook = cSheets


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Richard Buttrey" wrote in
message ...
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
__________________________