Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to save each sheet as a separate excel-file
Hi, Happy New Year Could anybody assist me with the code for saving each sheet to a separate (newly created) file? So that if I have an excel-file with 20 sheets, I would like a macro to create 20 separate files. The new files could get the same names as the sheets (preferably) or the names could be taken from a cell in the relevant sheet. Thanks for any help you can give. Peter |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to save each sheet as a separate excel-file
Assumes the workbook with the 20 sheets has been saved and is not in the
Root directory. Sub MakeWorkbooks() Dim sh as worksheet Dim sStr as String for each sh in thisworkbook.Worksheets sh.copy sStr = ThisWorkbook.Path & "\" & sh.name & ".xls" On Error Resume Next Kill sStr On Error goto 0 ActiveWorkbook.SaveAs sStr, xlWorkbookNormal ActiveWorkbook.Close Savechanges:=False Next End Sub -- Regards, Tom Ogilvy Audio_freak wrote in message . 2.2... Hi, Happy New Year Could anybody assist me with the code for saving each sheet to a separate (newly created) file? So that if I have an excel-file with 20 sheets, I would like a macro to create 20 separate files. The new files could get the same names as the sheets (preferably) or the names could be taken from a cell in the relevant sheet. Thanks for any help you can give. Peter |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to save each sheet as a separate excel-file
Try this Audio_freak
Sub test() Application.ScreenUpdating = False For a = 1 To Worksheets.Count Sheets(a).Copy ActiveWorkbook.SaveAs Sheets(1).Name ActiveWorkbook.Close Next a Application.ScreenUpdating = True End Sub -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "Audio_freak" wrote in message . 2.2... Hi, Happy New Year Could anybody assist me with the code for saving each sheet to a separate (newly created) file? So that if I have an excel-file with 20 sheets, I would like a macro to create 20 separate files. The new files could get the same names as the sheets (preferably) or the names could be taken from a cell in the relevant sheet. Thanks for any help you can give. Peter |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to save each sheet as a separate excel-file
Works ! Thanks!! "Tom Ogilvy" wrote in : Assumes the workbook with the 20 sheets has been saved and is not in the Root directory. Sub MakeWorkbooks() Dim sh as worksheet Dim sStr as String for each sh in thisworkbook.Worksheets sh.copy sStr = ThisWorkbook.Path & "\" & sh.name & ".xls" On Error Resume Next Kill sStr On Error goto 0 ActiveWorkbook.SaveAs sStr, xlWorkbookNormal ActiveWorkbook.Close Savechanges:=False Next End Sub -- Regards, Tom Ogilvy Audio_freak wrote in message . 2.2... Hi, Happy New Year Could anybody assist me with the code for saving each sheet to a separate (newly created) file? So that if I have an excel-file with 20 sheets, I would like a macro to create 20 separate files. The new files could get the same names as the sheets (preferably) or the names could be taken from a cell in the relevant sheet. Thanks for any help you can give. Peter |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to save each sheet as a separate excel-file
En deze werkt ook ! Stukje korter zelfs. Dank !! "Ron de Bruin" wrote in news:#hFAyuv0DHA.2856 @TK2MSFTNGP09.phx.gbl: Try this Audio_freak Sub test() Application.ScreenUpdating = False For a = 1 To Worksheets.Count Sheets(a).Copy ActiveWorkbook.SaveAs Sheets(1).Name ActiveWorkbook.Close Next a Application.ScreenUpdating = True End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Split text file into Excel sheet and separate the final results intoa new sheet | Excel Worksheet Functions | |||
How do I save a page in a workbook as a separate file? | Excel Worksheet Functions | |||
How do I save part of a worksheet in a separate file? | Excel Worksheet Functions | |||
Can I auto save to a separate file?(not the file I am working in) | Setting up and Configuration of Excel | |||
Excel kept on asking to save file every time I jump from one sheet to another! | Excel Worksheet Functions |