Excel save tabs as separate files.

In excel can tabs be saved as separate .xlsm files.
Refer to the attached file.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
This saves Sheet1 to C:\Temp.

Sub WB_From_Sheet()

'From http://www.rondebruin.nl/mail/folder2/mail2.htm

'Working in 2000-2010
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim wbSource As Workbook
Dim wsSource As Worksheet
Dim wbDest As Workbook
Dim FilePath As String
Dim FileName As String

    Set wbSource = ActiveWorkbook
    FilePath = "C:\Temp"

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    Set wsSource = wbSource.Sheets("Sheet1")
    ' Copy the sheet to a new workbook
    With wsSource
    End With
    ' ...so that's the workbook that is the ActiveWorkbook
    Set wbDest = ActiveWorkbook

    ' Determine the Excel version and file extension/format.
    With wbDest
        If Val(Application.Version) < 12 Then
            'You use Excel 2000-2003
            FileExtStr = ".xls": FileFormatNum = -4143
            'You use Excel 2007-2010, we exit the sub when your answer is
            'NO in the security dialog that you only see when you copy
            'a sheet from a xlsm file with macro's disabled.
            If wbSource.Name = .Name Then
                With Application
                    .ScreenUpdating = True
                    .EnableEvents = True
                End With
                MsgBox "Your answer is NO in the security dialog"
                Exit Sub
                Select Case wbSource.FileFormat
                Case 51: FileExtStr = ".xlsx": FileFormatNum = 51
                Case 52:
                    If .HasVBProject Then
                        FileExtStr = ".xlsm": FileFormatNum = 52
                        FileExtStr = ".xlsx": FileFormatNum = 51
                    End If
                Case 56: FileExtStr = ".xls": FileFormatNum = 56
                Case Else: FileExtStr = ".xlsb": FileFormatNum = 50
                End Select
            End If
        End If
    End With
    'Save the new workbook
    FileName = wsSource.Name & " " & Format(Now, "mm-dd-yyyy h-mm")

    With wbDest
        .SaveAs FilePath & "\" & FileName & FileExtStr, _
    End With

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With

End Sub

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial

Pls try

Sub SplitBySheet()

Dim strFolder As String

Application.ScreenUpdating = False

With Application.FileDialog(msoFileDialogFolderPicker)
        .AllowMultiSelect = False
        If .SelectedItems.Count > 0 Then
            strFolder = .SelectedItems(1)
            MsgBox " No folder choosed"
            Exit Sub
        End If
End With

For Each sh In ActiveWorkbook.Sheets
    ActiveWorkbook.SaveAs Filename:=sh.Name & ".xlsm", FileFormat:= _
        xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

Application.ScreenUpdating = True
End Sub 

Open in new window

1. Right-click on the tab, select, "Move or Copy"

2. In the "To Book" menu, select "(new book)"

3. Click the Microsoft button in the top-left, select "Save As" and you'll be able to create a new file (click the "Save as type" menu to select .xlsm).
You can use a vb script to do this, consider something like this, I have used this to do the same thing but save as separate PDF files.

Sub Create_PDF()
Dim tempPDFFileName As String
Dim tempPSFileName As String
Dim tempPDFRawFileName As String
Dim tempLogFileName As String
Dim wsEachSheet As Worksheet
Dim mypdfDist As New PdfDistiller
For Each wsEachSheet In ThisWorkbook.Worksheets

    tempPDFRawFileName = ThisWorkbook.Path & "\Saved Files\" & wsEachSheet.Name
    tempPSFileName = tempPDFRawFileName & ".ps"
    tempPDFFileName = tempPDFRawFileName & ".pdf"
    tempLogFileName = tempPDFRawFileName & ".log"

    wsEachSheet.PrintOut Copies:=1, preview:=False, ActivePrinter:="Adobe PDF", _
        printtofile:=True, Collate:=True, prtofilename:=tempPSFileName
    mypdfDist.FileToPDF tempPSFileName, tempPDFFileName, ""

 Kill tempPSFileName
    Kill tempLogFileName
Next wsEachSheet

Set mypdfDist = Nothing

End Sub

Open in new window

tesla764Author Commented:
These all look like great suggestions. I will be able to try them on Monday.
Thanks Everybody,
Have a great weekend.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.