How to Use Excel VBA to Save a Sheet as New Workbook without Opening It (6 Examples)

Example 1 – Renaming a Sheet and Saving It as a New Workbook

• Open your workbook in Excel.
• Press Alt + F11 to open the Visual Basic for Applications (VBA) editor.

• Click on Insert and select Module to create a new module.

• In the module, paste the following code:

Sub RenameAndSaveSheet()
Application.EnableEvents = False
Application.DisplayAlerts = False
ActiveWorkbook.Sheets(2).Copy
ActiveWorkbook.Sheets(1).Name = "SalesInfo"
ActiveWorkbook.SaveAs "C:\Users\DELL\Desktop\Nahian\blog 54\Rename_Sheet", FileFormat:=51
ActiveWorkbook.Close False
Application.EnableEvents = True
Application.DisplayAlerts = True
End Sub

• Run the code by clicking the Run button in the VBA Editor.

• Check the specified file location to find the newly created workbook named Rename_Sheet.

• Open it and the 2nd sheet of the previous workbook becomes a new workbook itself. The name of the 2nd sheet changes to SalesInfo.

Example 2 – Saving a Single Sheet as a New Workbook

• Follow the same steps to open the VBA Module.
• Paste the following code in the module:

Sub SaveSheetInNewWorkbook()
    Dim File_Name As String
    Dim File_Path As String
    File_Path = "C:\Users\DELL\Desktop\Nahian\blog 54\Save"
    File_Name = " Workbook Without Opening" & ".xlsx"
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    ThisWorkbook.Sheets("dataset").Copy
    If Dir(File_Path & "" & File_Name) <> "" Then
    MsgBox "File " & File_Path & "" & File_Name & " already exists"
    Else
    ActiveWorkbook.SaveAs Filename:=File_Path & "" & File_Name
    Application.ActiveWorkbook.Close False
    End If
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

• Run the code using the Run button in the VBA Editor.

• Check the specified file location for the newly created workbook named Workbook Without Opening.

• Open it and the dataset sheet of the previous workbook becomes a new workbook itself. 

Read More: How to Save a Worksheet as a New File Using Excel VBA

Example 3 – Saving Each Sheet as New Workbook

• Open your workbook in Excel.
• Press Alt + F11 to open the Visual Basic for Applications (VBA) editor.
• Click on Insert and select Module to create a new module.
• In the module, paste the following code:

Sub SaveMultipleSheets()
Dim location As String
location = "C:\Users\DELL\Desktop\Nahian\blog 54\Multiple Sheets\sheet_"
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each Sheet In ThisWorkbook.Sheets
    Sheet.Copy
    Application.ActiveWorkbook.SaveAs location & Sheet.Name & ".xlsx"
    Application.ActiveWorkbook.Close False
Next
Application.ScreenUpdating = True
End Sub

• Run the code by clicking the Run button in the VBA Editor.

• Check the specified file location to find individual workbooks for each sheet.

• Open the 1st workbook to see what actually happens.

.

You can save every sheet as a new workbook without opening it by using Excel VBA.

Example 4 – Saving Each Sheet as New Workbook Based on a Word or Text

• Follow the same steps to open the VBA Module.
• Paste the following code in the module:

Sub SaveSheetBasedOnWord()
Dim Target_Path As String
Dim Target_Text As String
Target_Text = "3"
Target_Path = "C:\Users\DELL\Desktop\Nahian\blog 54\Save Sheet Based on Word"
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each Sheet In ThisWorkbook.Sheets
If InStr(1, Sheet.Name, Target_Text, vbBinaryCompare) <> 0 Then
Sheet.Copy
Application.ActiveWorkbook.SaveAs _
Filename:=Target_Path & "\" & Sheet.Name & ".xlsx"
Application.ActiveWorkbook.Close False
End If
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

• Run the code using the Run button in the VBA Editor.

• Check the specified file location for the newly created workbook(s) based on the word or text.

• Open it and the dataset3 sheet of the previous workbook becomes a new workbook itself. Open it to see the content of this workbook.

Example 5 – Saving Multiple Sheets into a New Workbook

• Open your workbook in Excel.
• Press Alt + F11 to open the Visual Basic for Applications (VBA) editor.
• Click on Insert and select Module to create a new module.
• In the module, paste the following code:

Option Explicit

Sub SaveSelectedSheets()
Dim Workbook_Source As Workbook, Target_Workbook As Workbook
Dim Sheet_List As String
Dim Sheet_Array As Variant
Dim Array_Index As Long
On Error GoTo errHandle
Sheet_List = "dataset1:dataset2"
Sheet_Array = Split(Sheet_List, ":")
If UBound(Sheet_Array) = -1 Then Exit Sub
Set Workbook_Source = ThisWorkbook
Set Target_Workbook = Workbooks.Add
Application.DisplayAlerts = False
Application.ScreenUpdating = False
For Array_Index = LBound(Sheet_Array) To UBound(Sheet_Array)
ThisWorkbook.Worksheets(Sheet_Array(Array_Index)).Copy Target_Workbook.Worksheets(Target_Workbook.Worksheets.Count)
Next Array_Index
ChDir "C:\Users\DELL\Desktop\Nahian\blog 54"
ActiveWorkbook.SaveAs Filename:="C:\Users\DELL\Desktop\Nahian\blog 54\Save Specified Sheets with VBA.xlsx", CreateBackup:=False
ActiveWorkbook.Close False
Application.DisplayAlerts = True
Application.ScreenUpdating = True
CleanObjects:
Set Target_Workbook = Nothing
Set Workbook_Source = Nothing
Exit Sub
errHandle:
MsgBox "Error: " & Err.Description, vbExclamation
GoTo CleanObjects
End Sub

• Run the code by clicking the Run button in the VBA Editor.

• Check the specified file location to find the newly created workbook named Save Specified Sheets with VBA.

• Open it and the dataset1 and dataset2 sheets of the previous workbook are in a new workbook itself.

Example 6 – Using VBA to Save Selected Sheet as a New Workbook

• Open your workbook in Excel.
• Press Alt + F11 to open the Visual Basic for Applications (VBA) editor.
• Click on Insert and select Module to create a new module.
• In the module, paste the following code:

Sub SaveSheetByArrayVariable()
    Sheets(Array("dataset1", "dataset2", "dataset3")).Select
    Sheets("dataset1").Activate
    Sheets(Array("dataset1", "dataset2", "dataset3")).Copy
    ChDir "C:\Users\DELL\Desktop\Nahian\blog 54"
    ActiveWorkbook.SaveAs Filename:="C:\Users\DELL\Desktop\Nahian\blog 54\Save Sheets by VBA_2.xlsx", _
    CreateBackup:=False
    ActiveWorkbook.Close False
End Sub

• Run the code by clicking the Run button in the VBA Editor.

• Check the specified file location to find the newly created workbook named Save Sheets by VBA_2.

• Open it, and the dataset1, dataset2, and dataset3 sheets from the previous workbook are in a new workbook.

Practice Section

You are provided with the dataset of this file so that you can practice these methods.

Download Practice Workbook

You can download the practice workbook from here: