Create new workbooks from selected sheets in master workbook

[alyssa75]

Hi all - looking for some help with the following:


For each SELECTED worksheet in the activeworkbook, I want to create a new workbook, with values only (no formulas) and save it to the same path as the master workbook. Name of the new workbook would be the sheet name.


Thanks!

 

[AlphaFrog]

Sub Save_Selected_Sheets()
    
    Dim wb As Workbook, ws As Worksheet, Ext As String, ff As Integer
    
    Application.ScreenUpdating = False
    
    Select Case ThisWorkbook.FileFormat
        Case 50, 51, 52
            ff = 51
            Ext = ".xlsx"
        Case Else
            ff = ThisWorkbook.FileFormat
            Ext = ".xls"
    End Select
    
    For Each ws In ActiveWindow.SelectedSheets
        ws.Copy
        Set wb = ActiveWorkbook
        wb.Sheets(1).UsedRange.Value = wb.Sheets(1).UsedRange.Value
        On Error Resume Next
        wb.SaveAs Filename:=ThisWorkbook.Path & "\" & wb.Sheets(1).Name & Ext, _
                  FileFormat:=ff
        On Error GoTo 0
        wb.Close SaveChanges:=False
    Next ws
    
    Application.ScreenUpdating = True
    
    MsgBox "Selected sheets saved."

End Sub