Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy comments to several sheets in a workbook? | Excel Worksheet Functions | |||
copy a workbook from other workbook with lot of sheets wit... | Excel Discussion (Misc queries) | |||
Printing separate sheets on one paper sheet | Excel Discussion (Misc queries) | |||
Linking sheets to a summary sheet in workbook | Excel Discussion (Misc queries) | |||
Stubborn toolbars in Excel | Excel Discussion (Misc queries) |