You have got a workbook and you want to save each worksheet as different workbook – You can adopt following method to do so if worksheets are not many in number.
1. Right click on a worksheet tab
2. Move or Copy
3. Select new book
4. Save this new workbook
5. Do it for all 20 worksheets.
But what if sheets are too many to accomplish this by above method. Or if this is a repetitive task. You better adopt a macro approach.
1. Make a backup of your workbook.
2. Open your workbook and ALT+F11
3. Locate your Workbook name in Project Explorer Window
4. Right click on your workbook name > Insert > Module
5. Copy paste the Macro code given
6. Go back to your Workbook and ALT+F8 to display Macro Window
7. Run your Macro from here
8. Delete you Macro if the Macro was needed to be run only once.
9. Otherwise save your file as .xlsm if you intend to reuse Macro again.
' Vijay A Verma (eforexcel.com) Sub WorksheetsToWorkbooks() Dim Ws As Worksheet Dim Path As String, FileName As String, Extension As String Dim FileFormatCode As Long Dim Arr Application.ScreenUpdating = False Application.DisplayAlerts = False With Application.FileDialog(msoFileDialogFolderPicker) .InitialFileName = Application.DefaultFilePath & "\" .Title = "Select a location for Saving Worksheets" .Show If .SelectedItems.Count = 0 Then MsgBox "Cancelled" Exit Sub Else Path = .SelectedItems(1) & "\" End If End With Arr = Split(ThisWorkbook.FullName, ".") Extension = Arr(UBound(Arr)) Select Case Extension Case "xlsb": FileFormatCode = 50 Case "xlsx": FileFormatCode = 51 Case "xlsm": FileFormatCode = 52 Case "xls": FileFormatCode = 56 End Select For Each Ws In Worksheets FileName = Path & Ws.Name & "." & Extension Ws.Copy ActiveWorkbook.SaveAs FileName, FileFormat:=FileFormatCode ActiveWorkbook.Close Next Ws Application.ScreenUpdating = True Application.DisplayAlerts = True End Sub