Excel VBA: Save Sheet as New Workbook without Opening

The article will show you how to use Excel VBA save sheet as new workbook without opening. If your workbook has a lot of sheets and you want to work with individual or some of the selected sheets, it would be convenient for you to make a new workbook with those sheets. Creating new workbooks manually will be time consuming. We are going to apply some VBA codes to solve this issue which will save us a lot of time.

We have the following information and sheets in our working dataset.

excel vba save sheet as new workbook without opening


Download Practice Workbook


6 Examples of Excel VBA to Save Sheet as New workbook without Opening

1. Renaming Sheet and Save It as New workbook without Opening

You can change the name of a sheet of your current workbook and save it in a new workbook without opening it. Let’s go through the process below.

Steps:

  • First, go to Developer >> Visual Basic

  • The VBA Editor will open. After that, select Insert >> Module

excel vba save sheet as new workbook without opening

  • After that, type the following code in the Module.
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

Code Explanation

  • First, we named our Sub Procedure as RenameAndSaveSheet.
  • Then we set the EnableEvents and DisplayAlerts Applications as False.
  • We want to rename the 2nd sheet of our current workbook and save it as a new workbook. For that reason, first, we copied the 2nd sheet and then named it as SalesInfo.
  • We used VBA SaveAs Method to save the workbook.
  • After that, we define the file location for this sheet to be saved as a workbook.
  • Finally, run the code.
  • Go back to your sheet and run the Macro named RenameAndSaveSheet as it is your current Macro. You may run this code from the Run button on the VBA Editor.

excel vba save sheet as new workbook without opening

  • After that, go to your file location and you will see the Rename_Sheet.

  • Open it and you will see that the 2nd sheet of the previous workbook becomes a new workbook itself. You can also see that the name of the 2nd sheet changed to SalesInfo.

excel vba save sheet as new workbook without opening

Thus, you can rename a sheet and save it as a new workbook without opening it by Excel VBA.

Read More: Excel VBA to Print As PDF and Save with Automatic File Name


2. Saving a Single Sheet as a New Workbook

In this section, I’ll show you how to save a single sheet as a new workbook. Let’s go through the instructions below.

Steps:

  • Follow Section 1 to open the VBA Module.
  • Type the following code in the VBA 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

excel vba save sheet as new workbook without opening

Code Explanation

  • First, we named our Sub Procedure as SaveSheetInNewWorkbook.
  • Then we declare the variables File_Name and File_Path as String.
  • We set the file location and file name for those 2 variables.
  • Then we set the DisplayAlerts and ScreenUpdating Applications as False.
  • We want to save the 1st sheet of our current workbook which is the dataset sheet and save it as a new workbook. For that reason, first, we copied the dataset.
  • After that, we used a VBA If Statement for the directory path of our workbook.
  • We set the destination for this workbook and named it Save Workbook Without Opening.
  • Finally, we run the code.
  • Go back to your sheet and run the Macro named SaveSheetInNewWorkbook as it is your current Macro. You may run this code from the Run button on the VBA Editor.

  • After that, go to your file location and you will see the Save Workbook Without Opening workbook.

excel vba save sheet as new workbook without opening

  • Open it and you will see that the dataset sheet of the previous workbook becomes a new workbook itself

Thus, you can rename a sheet and save it as a new workbook without opening it by Excel VBA.

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


3. Saving Each Sheet as New Workbook

If you want to save all the sheets of your workbook as new workbooks, you need to follow the steps below of this section.

Steps:

  • Follow Section 1 to open the VBA Module.
  • Type the following code in the Module.
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

excel vba save sheet as new workbook without opening

Code Explanation

  • First, we named our Sub Procedure as SaveMultipleSheets.
  • Then we declare location as String.
  • We set the file location with this variable.
  • Then we set the DisplayAlerts and ScreenUpdating Applications as False.
  • After that, we used a For Loop to copy all the sheets of this workbook and save them in the location.
  • We used VBA SaveAs Method to save the workbook.
  • Finally, we run the code.
  • Go back to your sheet and run the Macro named SaveMultipleSheets as it is your current Macro. You may run this code from the Run button on the VBA Editor.

  • After that, go to your file location and you will see that every sheet has become a workbook.

excel vba save sheet as new workbook without opening

  • I’m going to open the 1st workbook to show you that this actually happened.

.

Thus, you can save every sheet as a new workbook without opening it by Excel VBA.

Read More: How to Save a Worksheet in Excel (2 Handy Approaches)


Similar Readings


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

If you want to save all the sheets of your workbook as new workbooks based on a word or text that is in the sheet name, you need to follow the steps below of this section.

Steps:

  • Follow Section 1 to open the VBA Module.
  • Type 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

excel vba save sheet as new workbook without opening

Code Explanation

  • First, we named our Sub Procedure as SaveSheetBasedOnWord.
  • Then we declare the variables Target_Path and Target_Text as String.
  • After that, we set the file location with this variable.
  • We want to save the 3rd sheet as a new workbook. The sheet has ‘3’ in its name. As we want to save the sheet based on a text it has in its name, we set the Target_Text as 3.
  • Later, we set Target_Path as the folder location.
  • Then we used a For Loop to split the sheet based on its text.
  • After that, we used the Copy method to copy that specific sheet to a new workbook.
  • The SaveAs method is used to Save the sheet that has a 3 in its name as a new workbook.
  • Finally, we run the code.
  • Go back to your sheet and run the Macro named SaveSheetBasedOnWord as it is your current Macro. You may run this code from the Run button on the VBA Editor.

  • After that, go to your file location and you will see the dataset3 workbook.

excel vba save sheet as new workbook without opening

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

Thus, you can save a sheet as a new workbook based on a word or text.

Read More: Save Excel Files as Text Without Quotes (4 Methods)


5. Saving Multiple Sheets into a New Workbook

You can also create a new workbook with some of the sheets of your current workbook. Let’s go through the process below for better understanding.

Steps:

  • Follow Section 1 to open the VBA Module.
  • Type the following code in the Module.
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

excel vba save sheet as new workbook without opening

Code Explanation

  • First, we named our Sub Procedure as SaveSelectedSheets.
  • Then we declare the variables Workbook_Source and Target_Workbook as Workbook.
  • After that, we declared Sheet_List As String, Sheet_Array As Variant and Array_Index As Long.
  • We want to save the 3rd sheet as a new workbook. The sheet has ‘3’ in its name. As we want to save the sheet based on a text it has in its name, we set the Target_Text as 3.
  • Later, we set an Error Handler.
  • Then we define which sheets will be together as a workbook. Here, we want to save dataset1 and dataset2 sheets in a new workbook. So we put them in the Sheet_List variable.
  • After that, we set Workbook_Source as ThisWorkbook and Target_Workbook asAdd to create a new workbook in the desired location of the hard drive.
  • A For Loop is used to count how many sheets we will transfer in the new saved workbook and copy them
  • After that, we used a ChDir statement to change the default directory of the sheets of the current workbook. Also we set the name of the new workbook and its location where it will be saved.
  • We put some other necessary statements.
  • Finally, we run the code.
  • Go back to your sheet and run the Macro named SaveSelectedSheets as it is your current Macro. You may run this code from the Run button on the VBA Editor.

  • After that, go to your file location and you will see the Save Specified Sheets with VBA.

excel vba save sheet as new workbook without opening

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

Thus, you can save multiple sheets as a new workbook without opening it.

Read More: How to Save Macro to Personal Macro Workbook?


6. Using VBA to Save Selected Sheet as New Workbook without Opening

You can use the VBA Selection Property and Array Variable to save the sheets you want in a new workbook. Please follow the steps below.

Steps:

  • Follow Section 1 to open the VBA Module.
  • Type the following code in the Module.
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

excel vba save sheet as new workbook without opening

Code Explanation

  • First, we use the Selection Property to select the dataset1, dataset2 and dataset3 sheets as Array variables.
  • Then we activate dataset1. You may activate any of those 3
  • We used a ChDir statement to change the default directory of the sheets of the current workbook. Also we set the name of the new workbook and its location where it will be saved.
  • We used VBA SaveAs Method to save the workbook.
  • Finally, we run the code.
  • Go back to your sheet and run the Macro named SaveSheetByArrayVariable as it is your current Macro. You may run this code from the Run button on the VBA Editor.

excel vba save sheet as new workbook without opening

  • After that, go to your file location and you will see the Save Sheets by VBA_2 workbook.

  • Open it and you will see the dataset1, dataset2 and dataset3 sheets of the previous workbook in a new workbook.

excel vba save sheet as new workbook without opening

Thus, you can rename a sheet and save it as a new workbook without opening it by Excel VBA.

Read More: How to Save Multiple Excel Sheets as One File (5 Easy Methods)


Practice Section

Here I’m giving you the dataset of this file so that you can practice these methods on your own.


Conclusion

Suffice to say, you will learn the best possible methods on how to save sheet as new workbook without opening it in Excel. If your workbook contains too many sheets, the application of these methods can make that complex situation much much simpler. If you have any questions or ideas or feedback regarding this article, please share them in the comment box. This will help me enrich my upcoming articles.


Related Articles

Nahian

Nahian

Hello, Nahian here! I do enjoy my efforts to help you understand some little basics on Microsoft Excel I've completed my graduation in Electrical & Electronic Engineering from BUET and I want to be a successful engineer in my life through intellect and hard-work, and that is the goal of my career.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo