Excel save tabs as separate files.

In excel can tabs be saved as separate .xlsm files.
Refer to the attached file.
Who is Participating?
Martin LissConnect With a Mentor Older 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

Rgonzo1971Connect With a Mentor Commented:

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

epichero22Connect With a Mentor Commented:
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).
ScorchDConnect With a Mentor Commented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.