Copy workbook

[Mark]

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

 

[Ron de Bruin]

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

 

[Mark]

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" <[email protected]> wrote in

message news:[email protected]...

 

[Ron de Bruin]

I see, I don't read it good

Why don't you do a simple File>SaveCopAs
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

 

[mark]

thanks Again for this

can you let me know where i put the code?

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

Why don't you do a simple File>SaveCopAs
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" <[email protected]> wrote in

message news:[email protected]...

 

[Ron de Bruin]

can you let me know where i put the code?

I like to know this

Do you want a example that mail the file also ?
Which mail program do you use ?
How many budget holders are there, do you have a list on a sheet with them
What are the names of the four sheets that you not want to send?

 

[mark]

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 File>SaveCopAs
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" <[email protected]> wrote in

message news:[email protected]...

 

[Ron de Bruin]

Hi Mark

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

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

 

[mark]

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" <[email protected]> wrote in

message news:[email protected]...

 

[Ron de Bruin]

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