| 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 . . |
|
#9
|
|||
|
|||
|
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 . . . |
|
#10
|
|||
|
|||
|
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 . . . |
| 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 | |||
Linear Mode
