| Home |
| Search |
| Today's Posts |
|
|
|
#1
|
|||
|
|||
Copy workbook
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 |
|
#2
|
|||
|
|||
|
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 ... 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 |
|
#3
|
|||
|
|||
|
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 ... 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 . |
|
#4
|
|||
|
|||
|
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 ... 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 . |
|
#5
|
|||
|
|||
|
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 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 ... 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 . . |
|
#6
|
|||
|
|||
|
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? -- Regards Ron de Bruin http://www.rondebruin.nl "mark" wrote in message ... 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 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 ... 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 . . |
|
#7
|
|||
|
|||
|
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 ... 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 . . |
|
#8
|
|||
|
|||
|
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 ... 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 . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| copy a sheet in same workbook temporary block excel | Excel Worksheet Functions | |||
| What's the simplest way to copy data from another workbook | Excel Worksheet Functions | |||
| copy COLUMN from 1 worksheet to another (in a different workbook) | Excel Discussion (Misc queries) | |||
| copy a workbook from other workbook with lot of sheets wit... | Excel Discussion (Misc queries) | |||
| copy worksheet from closed workbook to active workbook using vba | Excel Worksheet Functions | |||
Hybrid Mode
