Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Omzala
 
Posts: n/a
Default How do I save sheets in a workbook to separate files?

I have a wokbook with MANY sheets. I was wondering if there was a quick way
to save the sheets into separate files (perhaps using tab names) whilst
retaining each ones header and footer.
  #2   Report Post  
Ron de Bruin
 
Posts: n/a
Default

Try this example Omzala
Copy this macro in a normal module

Sub Copy_All_Sheets_To_New_Workbook()
Dim WbMain As Workbook
Dim Wb As Workbook
Dim sh As Worksheet
Dim DateString As String
Dim FolderName As String

Application.ScreenUpdating = False
DateString = Format(Now, "yy-mm-dd hh-mm-ss")
Set WbMain = ThisWorkbook
MkDir WbMain.Path & "\" & WbMain.Name & " " & DateString
FolderName = WbMain.Path & "\" & WbMain.Name & " " & DateString

For Each sh In WbMain.Worksheets
If sh.Visible = -1 Then
sh.Copy
Set Wb = ActiveWorkbook
Wb.SaveAs FolderName _
& "\" & Wb.Sheets(1).Name & ".xls"
Wb.Close False
Set Wb = Nothing
End If
Next sh
MsgBox "Look in " & FolderName & " for the files"
Application.ScreenUpdating = True
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Omzala" wrote in message ...
I have a wokbook with MANY sheets. I was wondering if there was a quick way
to save the sheets into separate files (perhaps using tab names) whilst
retaining each ones header and footer.



  #3   Report Post  
Gord Dibben
 
Posts: n/a
Default

Omzala

Sub Make_New_Books()
Dim w As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each w In ActiveWorkbook.Worksheets
w.Copy
ActiveWorkbook.SaveAs FileName:=ThisWorkbook.Path & "\" & w.Name
ActiveWorkbook.Close
Next w
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub


Gord Dibben Excel MVP

On Thu, 13 Jan 2005 07:21:04 -0800, Omzala
wrote:

I have a wokbook with MANY sheets. I was wondering if there was a quick way
to save the sheets into separate files (perhaps using tab names) whilst
retaining each ones header and footer.


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
Copy comments to several sheets in a workbook? jen_l_333 Excel Worksheet Functions 1 January 7th 05 11:30 PM
copy a workbook from other workbook with lot of sheets wit... Vai Excel Discussion (Misc queries) 1 January 3rd 05 11:27 PM
Printing separate sheets on one paper sheet Attyla Excel Discussion (Misc queries) 2 December 19th 04 06:58 PM
Linking sheets to a summary sheet in workbook gambinijr Excel Discussion (Misc queries) 4 December 16th 04 09:13 PM
Stubborn toolbars in Excel 007 Excel Discussion (Misc queries) 9 December 11th 04 03:02 PM


All times are GMT +1. The time now is 05:43 PM.

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"