Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto saving sheets from a workbook as separate files.
Hi I need some help with some VBA code. My workbook has 50 sheets. Numbered 1 - 50. I'd like to have a macro save each sheet as a separate file to a path and in a format specified by response to a popup. Can someone assist with this? Grateful for any help. |
#2
|
|||
|
|||
Quote:
example: if you want to save as xlsx, then enter only 51 xlsx - 51, xlsm - 52,xls - 56,xlsb - 50. you can play with it and modify accordingly. 'bala sesharao created to save the worksheets as seperate workbooks Private Sub Workbook_Open() Call Copy_Every_Sheet_To_New_Workbook End Sub Sub Copy_Every_Sheet_To_New_Workbook() 'Working in 97-2007 Dim FileExtStr As String Dim FileFormatNum As Long Dim Sourcewb As Workbook Dim Destwb As Workbook Dim sh As Worksheet Dim DateString As String Dim FolderName As String With Application .ScreenUpdating = False .EnableEvents = False .Calculation = xlCalculationManual End With 'Copy every sheet from the workbook with this macro Set Sourcewb = ThisWorkbook 'Create new folder to save the new files in DateString = Format(Now, "yyyy-mm-dd hh-mm-ss") FolderName = Sourcewb.Path & "\" & Sourcewb.Name & " " & DateString MkDir FolderName 'Copy every visible sheet to a new workbook For Each sh In Sourcewb.Worksheets 'If the sheet is visible then copy it to a new workbook If sh.Visible = -1 Then sh.Copy 'Set Destwb to the new workbook Set Destwb = ActiveWorkbook 'Determine the Excel version and file extension/format With Destwb End With 'Change all cells in the worksheet to values if you want If Destwb.Sheets(1).ProtectContents = False Then With Destwb.Sheets(1).UsedRange .Cells.Copy .Cells.PasteSpecial xlPasteValues .Cells(1).Select End With Application.CutCopyMode = False End If FileFormatNum = InputBox("enter the version number for", "versioning", "xlsx - 51, xlsm - 52,xls - 56,xlsb - 50") 'Save the new workbook and close it With Destwb .SaveAs FolderName _ & "\" & Destwb.Sheets(1).Name & FileExtStr, _ FileFormat:=FileFormatNum .Close False End With End If GoToNextSheet: Next sh MsgBox "You can find the files in " & FolderName With Application .ScreenUpdating = True .EnableEvents = True .Calculation = xlCalculationAutomatic End With End Sub all the best
__________________
Thanks Bala Last edited by bala_vb : May 16th 10 at 08:20 AM |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto saving sheets from a workbook as separate files.
Start here
http://www.rondebruin.nl/copy6.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Colin Hayes" wrote in message ... Hi I need some help with some VBA code. My workbook has 50 sheets. Numbered 1 - 50. I'd like to have a macro save each sheet as a separate file to a path and in a format specified by response to a popup. Can someone assist with this? Grateful for any help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Saving just 1 worksheet out of a workbook to a separate file | Excel Discussion (Misc queries) | |||
Select sheet tabs in workbook & save to separate workbook files | Excel Worksheet Functions | |||
Saving Sheets into Multiple Files | Excel Discussion (Misc queries) | |||
workbook sheets into separate files | Excel Worksheet Functions | |||
How do I save sheets in a workbook to separate files? | Excel Worksheet Functions |