Save Sheet as New Workbook (Macro)

This macro will save a specified sheet as a new workbook:

Sub Save_Sheet()

‘Change the text “Sheet1” to the name of the sheet you wish to save

   ThisWorkbook.Sheets("Sheet1").Copy

   With ActiveSheet.UsedRange

       .Copy

       .PasteSpecial xlValues

   End With

‘The “DisplayAlerts = False” will stop Excel from displaying the “Save Dialog”

   Application.DisplayAlerts = False

   Application.CutCopyMode = False

‘Change the text “C:\temp\Saved” to the new location you wish to save the spreadsheet

‘Note that “Saved” is the name of the spreadsheet

   ActiveWorkbook.SaveAs "C:\temp\Saved"

‘This will close the workbook after saving

‘You can remove this line if you want the new workbook to stay open

   ActiveWorkbook.Close

End Sub