Hi Mark

It is working for me

This must be one line

wb.Sheets(Array
("Omagh", "Ballymena", "Portadown")).Delete

Are you sure that you don't have a space before one of the sheet names
Send me your test workbook private and I will look at it


--
Regards Ron de Bruin
http://www.rondebruin.nl



"mark" wrote in message ...
Still no joy, maybe I am inputting this incorrectly.
This is the procedures I take to input this code.
Open workbook, right click on icon beside file, and
scroll down to highlight view code and open.
MBV opens, I then go to the code sheet and hit the down
arrow in order to highlight workbook I then enter the
following.

Private Sub Workbook_Open()
Dim wb As Workbook
'copy all sheets
Worksheets.Copy
Set wb = ActiveWorkbook
Application.DisplayAlerts = False
'delete the sheets you want
wb.Sheets(Array
("Omagh", "Ballymena", "Portadown")).Delete
Application.DisplayAlerts = True



End Sub

The following error appears.
Run time error '1004'
Application-defined or object defined error.
The new worksheet will appear but all sheets from
original are in the

Mark
-----Original Message-----
Hi Mark

wb.Sheets(Array
("Omagh", "Ballymena", "Portadown")).Delete

It is possible that you have a space before your
sheetname
Check this out


--
Regards Ron de Bruin
http://www.rondebruin.nl



"mark" wrote in
message ...
This code works fine if the names on the workbook hav
not
changed, however in sheet 1 i have Omagh, in sheet 2 i
have Ballymena, in sheet 3 i have Portadown. if i amend
the code below it comes up with the following error
message. Runtime error '9' suscript out of range. can
you
please help.

thanks.

mark
-----Original Message-----
I see, I don't read it good

Why don't you do a simple FileSaveCopAs
And delete the four sheets


With code Use this

Dim wb As Workbook
'copy all sheets
Worksheets.Copy
Set wb = ActiveWorkbook
Application.DisplayAlerts = False
'delete the sheets you want
wb.Sheets(Array("Sheet1", "Sheet3")).Delete
Application.DisplayAlerts = True

Now you have a workbook with the sheets you want
This will not copy modules and code in your
thisworkbook
If you want this post back

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Mark" wrote in
message ...
Thanks a lot for this
But what i need is a duplicate workbook to send out
to
the budget holders.
thanks
-----Original Message-----
Hi Mark

You can do the copy like this
http://www.rondebruin.nl/copy6.htm

But maybe this is easier to copy and mail in one step
http://www.rondebruin.nl/mail/folder1/mail5.htm

Or with Outlook only
http://www.rondebruin.nl/mail/folder2/mail5.htm

Or with CDO
http://www.rondebruin.nl/cdo.htm#attachment

Or with a template
http://www.rondebruin.nl/mail/templates.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Mark" wrote in
message news:70eb01c52615$0fdb0db0
...
I have a budget workbook with 26 sheets which gets
update
during every month. At the end of the month i need
to
copy all sheets to a new workbook in order to send
this
to all budget holders (i do not send out the
original
due
to audit restrictions).
is there any way i could set up a command button so
that
it would copy all sheets except 4 to a new
workbook.
this would save alot of time and effort,
thanks for your help.

Mark


.



.



.