| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save Every Worksheet as its own Workbook
Pretty straight forward I hope...need to save every worksheet in a workbook as its own workbook (name of each file will obviously be same as the name of each worksheet) TIA *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Save Every Worksheet as its own Workbook
Hi Michael
Try this http://www.rondebruin.nl/copy6.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Michael Smith" wrote in message ... Pretty straight forward I hope...need to save every worksheet in a workbook as its own workbook (name of each file will obviously be same as the name of each worksheet) TIA *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Save Every Worksheet as its own Workbook
Thanks Ron...So so close....but still not there yet. Your code
definetly helped but I didn't need the dating and folder creation part of your code....am I close with this?? I am getting hungup on naming the file and continuing on to the other sheets in my workbook. Dim Wb As Workbook Dim sh As Worksheet Application.ScreenUpdating = False Application.EnableEvents = False Set Wb = ThisWorkbook For Each sh In Wb.Worksheets Sheets(sh).Select Sheets(sh).Copy ChDir "C:\temp" ActiveWorkbook.SaveAs Filename:="C:\temp\sh.xls" Next sh Application.ScreenUpdating = True Application.EnableEvents = True *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Save Every Worksheet as its own Workbook
No not close
sh is a reference to the sheet and you don't have to select it also You can use sh.copy Try this one Sub Copy_All_Sheets_To_New_Workbook2() Dim WbMain As Workbook Dim Wb As Workbook Dim sh As Worksheet Application.ScreenUpdating = False Application.EnableEvents = False Set WbMain = ThisWorkbook For Each sh In WbMain.Worksheets If sh.Visible = -1 Then sh.Copy Set Wb = ActiveWorkbook Wb.SaveAs "C:\Temp\" & Wb.Sheets(1).Name & ".xls" Wb.Close False End If Next sh Application.ScreenUpdating = True Application.EnableEvents = True End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "Michael Smith" wrote in message ... Thanks Ron...So so close....but still not there yet. Your code definetly helped but I didn't need the dating and folder creation part of your code....am I close with this?? I am getting hungup on naming the file and continuing on to the other sheets in my workbook. Dim Wb As Workbook Dim sh As Worksheet Application.ScreenUpdating = False Application.EnableEvents = False Set Wb = ThisWorkbook For Each sh In Wb.Worksheets Sheets(sh).Select Sheets(sh).Copy ChDir "C:\temp" ActiveWorkbook.SaveAs Filename:="C:\temp\sh.xls" Next sh Application.ScreenUpdating = True Application.EnableEvents = True *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Save Every Worksheet as its own Workbook
The collection Sheets(1) won't work because my sheets have already been named....Any way to just name the file the same as the current sheet name? Thanks *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Save Every Worksheet as its own Workbook
It is doing that
Have you try the macro ??? -- Regards Ron de Bruin http://www.rondebruin.nl "Michael Smith" wrote in message ... The collection Sheets(1) won't work because my sheets have already been named....Any way to just name the file the same as the current sheet name? Thanks *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Save Every Worksheet as its own Workbook
yes I tried it... ..the code runs through to completion...but only
creates 1 file, named Sheet1 and no data is copied into the file. Sub Copy_All_Sheets_To_New_Workbook() Dim WbMain As Workbook Dim Wb As Workbook Dim sh As Worksheet Application.ScreenUpdating = False Application.EnableEvents = False Set WbMain = ThisWorkbook For Each sh In WbMain.Worksheets Application.WindowState = xlMinimized If sh.Visible = -1 Then sh.Copy Set Wb = ActiveWorkbook Wb.SaveAs "C:\Temp\" & Wb.Sheets(1).Name & ".xls" Wb.Close False End If Next sh Application.ScreenUpdating = True Application.EnableEvents = True End Sub *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Save Every Worksheet as its own Workbook
Did you copy the code in your personal.xls ?
-- Regards Ron de Bruin http://www.rondebruin.nl "Michael Smith" wrote in message ... yes I tried it... ..the code runs through to completion...but only creates 1 file, named Sheet1 and no data is copied into the file. Sub Copy_All_Sheets_To_New_Workbook() Dim WbMain As Workbook Dim Wb As Workbook Dim sh As Worksheet Application.ScreenUpdating = False Application.EnableEvents = False Set WbMain = ThisWorkbook For Each sh In WbMain.Worksheets Application.WindowState = xlMinimized If sh.Visible = -1 Then sh.Copy Set Wb = ActiveWorkbook Wb.SaveAs "C:\Temp\" & Wb.Sheets(1).Name & ".xls" Wb.Close False End If Next sh Application.ScreenUpdating = True Application.EnableEvents = True End Sub *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Save Every Worksheet as its own Workbook
Yes
*** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Save Every Worksheet as its own Workbook
Read the code good
Set WbMain = ThisWorkbook Thisworkbook is the personal.xls file now because there is the code Change it to Set WbMain = Activeworkbook -- Regards Ron de Bruin http://www.rondebruin.nl "Michael Smith" wrote in message ... Yes *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Save Every Worksheet as its own Workbook
I hate obvious solutions...they make you feel extra dumb. All hail king
ron. Thank you, thank you, thank you. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Save Every Worksheet as its own Workbook
ron. Thank you, thank you, thank you
You are welcome -- Regards Ron de Bruin http://www.rondebruin.nl "Michael Smith" wrote in message ... I hate obvious solutions...they make you feel extra dumb. All hail king ron. Thank you, thank you, thank you. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Save Worksheet as new workbook with specifics | Excel Discussion (Misc queries) | |||
| How do I save a worksheet within a workbook? | Excel Discussion (Misc queries) | |||
| Save Worksheet vs Workbook | Excel Worksheet Functions | |||
| Save Workbook or worksheet | Excel Programming | |||
| save worksheet as new workbook | Excel Programming | |||
Linear Mode
