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 it. If your workbook has a lot of sheets and you want to work with an 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.

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 using Excel VBA. Let’s go through the process below.

Steps:

First, go to Developer >> Visual Basic

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

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.

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.

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

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

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.

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.

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 in 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

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.

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.

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 in 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

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.

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.

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 a 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

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 the 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.

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.

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

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.

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.

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

Practice Section

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

Download Practice Workbook

VBA to Save Sheet as New Workbook.xlsm

Conclusion

Suffice it 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 simpler. If you have any questions, ideas, or feedback regarding this article, please share them in the comment box. This will help me enrich my upcoming articles.

Get FREE Advanced Excel Exercises with Solutions!

2 Comments

Zapper Dec 13, 2023 at 6:17 PM

Hi,

Could you please write a code that how to execute option # 6 with a Command Button.

Thanks
- Reply
  Lutfor Rahman Shimanto Dec 14, 2023 at 12:41 PM
  Hello ZAPPER
  
  Thanks for reading ExcelDemy Blogs. You wanted a button to click and copy some sheets in a folder.
  
  I am delighted to inform you that I have enhanced the sub-procedure so you will like it. The sub-procedure will create a folder dynamically, and the path will be the workbook path. Besides, it will handle errors as well. I have inserted a print icon and assigned the sub-procedure as follows.
  
  OUTPUT OVERVIEW:
  
  Enhanced Excel VBA Sub-procedure:
```
Sub AdvancedSaveSheetByArrayVariable()

    Dim currentPath As String
    currentPath = ThisWorkbook.Path
    
    Dim folderPath As String
    folderPath = currentPath & "\" & "SavedSheetsFolder"
    
    On Error GoTo ErrHandle
    
    MkDir folderPath
    
    Sheets(Array("dataset1", "dataset2", "dataset3")).Select
    Sheets("dataset1").Activate
    Sheets(Array("dataset1", "dataset2", "dataset3")).Copy

    With ActiveWorkbook
        .SaveAs Filename:=folderPath & "\" & "SaveSheetsByVBA_2.xlsx", CreateBackup:=False
        .Close False
    End With
    
    Exit Sub

ErrHandle:

    MsgBox "Folder and File Exist. Remove the previous folder and file.", vbCritical

End Sub
```
  I am attaching the solution workbook for better understanding. Good luck.
  
  DOWNLOAD WORKBOOK
  
  Regards
  Lutfor Rahman Shimanto
  ExcelDemy

Excel VBA: Save Sheet as New Workbook without Opening

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

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

2. Saving a Single Sheet as a New Workbook

3. Saving Each Sheet as New Workbook

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

5. Saving Multiple Sheets into a New Workbook

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

Practice Section

Conclusion

Leave a reply Cancel reply