Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Separating worksheets into separate workbooks
I have a file with about 100 tabs (worksheets) and would like to save each
worksheet as a separate workbook without going through the move function 100 times. Is there anyway I can do a mass separation of the worksheets? thanks in advance. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Separating worksheets into separate workbooks
Cobra, this macro should do what you want,
Sub Copy_Sheets_As_New_Workbook () 'will take each sheet in the workbook and save it into their own work book 'by sheet name, so sheet1 will become sheet1.xls, will over write files if there is one Dim ws As Worksheet Application.ScreenUpdating = False Application.DisplayAlerts = False For Each ws In ActiveWorkbook.Worksheets ws.Copy ActiveWorkbook.SaveAs FileName:=ThisWorkbook.Path & "\" & ws.Name ActiveWorkbook.Close Next ws Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Cobra" wrote in message ... I have a file with about 100 tabs (worksheets) and would like to save each worksheet as a separate workbook without going through the move function 100 times. Is there anyway I can do a mass separation of the worksheets? thanks in advance. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Separating worksheets into separate workbooks
Paul,
Thank you. Works great!! "Paul B" wrote: Cobra, this macro should do what you want, Sub Copy_Sheets_As_New_Workbook () 'will take each sheet in the workbook and save it into their own work book 'by sheet name, so sheet1 will become sheet1.xls, will over write files if there is one Dim ws As Worksheet Application.ScreenUpdating = False Application.DisplayAlerts = False For Each ws In ActiveWorkbook.Worksheets ws.Copy ActiveWorkbook.SaveAs FileName:=ThisWorkbook.Path & "\" & ws.Name ActiveWorkbook.Close Next ws Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Cobra" wrote in message ... I have a file with about 100 tabs (worksheets) and would like to save each worksheet as a separate workbook without going through the move function 100 times. Is there anyway I can do a mass separation of the worksheets? thanks in advance. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Separating worksheets into separate workbooks
Your Welcome
-- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Cobra" wrote in message ... Paul, Thank you. Works great!! "Paul B" wrote: Cobra, this macro should do what you want, Sub Copy_Sheets_As_New_Workbook () 'will take each sheet in the workbook and save it into their own work book 'by sheet name, so sheet1 will become sheet1.xls, will over write files if there is one Dim ws As Worksheet Application.ScreenUpdating = False Application.DisplayAlerts = False For Each ws In ActiveWorkbook.Worksheets ws.Copy ActiveWorkbook.SaveAs FileName:=ThisWorkbook.Path & "\" & ws.Name ActiveWorkbook.Close Next ws Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Cobra" wrote in message ... I have a file with about 100 tabs (worksheets) and would like to save each worksheet as a separate workbook without going through the move function 100 times. Is there anyway I can do a mass separation of the worksheets? thanks in advance. |
#5
|
|||
|
|||
Paul,
What code would need to be added so that in each of those separate workbooks generated, the worksheet is copy and paste special values? I have a workbook with separate worksheets of pivot tables. When I split the worksheets into separate workbooks I want to copy and paste special values, so that I can't manipulate the pivot tables. Thanks Jeremy "Paul B" wrote: Cobra, this macro should do what you want, Sub Copy_Sheets_As_New_Workbook () 'will take each sheet in the workbook and save it into their own work book 'by sheet name, so sheet1 will become sheet1.xls, will over write files if there is one Dim ws As Worksheet Application.ScreenUpdating = False Application.DisplayAlerts = False For Each ws In ActiveWorkbook.Worksheets ws.Copy ActiveWorkbook.SaveAs FileName:=ThisWorkbook.Path & "\" & ws.Name ActiveWorkbook.Close Next ws Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Cobra" wrote in message ... I have a file with about 100 tabs (worksheets) and would like to save each worksheet as a separate workbook without going through the move function 100 times. Is there anyway I can do a mass separation of the worksheets? thanks in advance. [/quote] |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how can i import worksheets from multiple workbooks | New Users to Excel | |||
Need2 batch print all the workbooks in 25 worksheets in one step | Excel Discussion (Misc queries) | |||
Question : Workbooks or Worksheets? | Excel Worksheet Functions | |||
Linking set of worksheets between different workbooks! | Excel Discussion (Misc queries) | |||
Converting worksheets to workbooks. Is there an easy way? | Excel Discussion (Misc queries) |