That is what I do when I add new workbooks, don't know why but it seems the
'right' way.

--
HTH

Bob Phillips

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

"Richard Buttrey" wrote in
message ...
Excellent,

Thanks Bob.

I think I'll capture the value for the number of new sheets, and then
reinstate it when the macro finishes as you suggest.

Rgds


On Fri, 11 Aug 2006 14:36:10 +0100, "Bob Phillips"
wrote:

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

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________