Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 709
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 709
Default 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   Report Post  
Junior Member
 
Posts: 1
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how can i import worksheets from multiple workbooks davidp23 New Users to Excel 1 September 1st 06 08:49 PM
Need2 batch print all the workbooks in 25 worksheets in one step richie s. Excel Discussion (Misc queries) 2 June 26th 06 03:46 PM
Question : Workbooks or Worksheets? WTG Excel Worksheet Functions 1 March 1st 06 10:14 PM
Linking set of worksheets between different workbooks! gp Excel Discussion (Misc queries) 0 March 22nd 05 10:27 PM
Converting worksheets to workbooks. Is there an easy way? Jim Excel Discussion (Misc queries) 1 March 22nd 05 03:31 PM


All times are GMT +1. The time now is 10:59 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"