Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Save Multiple Tabs in Excel as Separate Files (5 Easy Methods)

If you want to save multiple tabs in Excel as separate files, you have come to the right place. Here, we will walk you through 5 easy and effective methods to do the task effortlessly.

Download Practice Workbook


5 Methods to Save Multiple Tabs in Excel as Separate Files

The following Excel workbook contains 6 worksheets. We want to separate these sheets and save multiple tabs in Excel as separate files. Here, we will use Excel 365. You can use any available Excel version.

how to save multiple tabs in excel as separate files


Method-1: Use of Copy and Paste Option to Save Multiple Tabs in Excel as Separate Files

In this method, we will use the Copy and Paste option to save multiple tabs in Excel as separate files.

Steps:

  • First, we will open the January-2022 worksheet.
  • Then,  we will click on the top left corner triangle which is marked with a red color box to select the entire sheet, or we will use the keyboard shortcut CTRL+A to select the entire worksheet.

how to save multiple tabs in excel as separate files

  • After that, we will right-click >> then select Copy from the Context Menu.

We can also type CTRL+C to copy the entire sheet.

Now, we will paste this into a new worksheet.

  • Afterward, we will go to the File tab.

  • Then, we will click on New >> select Blank workbook.

  • After that, we will click on the top left corner triangle which is marked with a red color box.

how to save multiple tabs in excel as separate files

We can see that the entire worksheet has been selected.

  • Then, we will right-click >> select Paste from the Context Menu.
  • We can also type CTRL+V to paste.

Finally, we can see our data from sheet January-2022 to a different workbook named Book2 and in Sheet1.

how to save multiple tabs in excel as separate files

Read More: How to Save a Worksheet in Excel to a Separate File (3 Handy Methods)


Method-2: Using Move or Copy Feature to Save Multiple Tabs in Excel as Separate Files

This is a quick method to save multiple tabs in Excel as separate files.

Steps:

  • First, we will open our February-2022 sheet.
  • After that, we will right-click on the sheet name February-2022 >> we will select Move or Copy from the Context Menu.

A Move or Copy window will appear.

  • Then, we will select a new book in the To book box >> mark Create a copy box >> click OK.

how to save multiple tabs in excel as separate files

Finally, we can see that February-2022 is now in Book3 Workbook.

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


Method-3: Applying VBA to Save Multiple Tabs as Separate Files

In this method, we will use VBA code to Save Multiple Tabs in Excel as Separate Files.

Steps:

  • First, we will save our Excel file as Save Multiple Tabs in Separate Excel Files, and we will keep this file in a folder named Folder to Keep Sheets where we want to see our output by using the VBA.

  • After that, we will open our Workbook >> go to the Developer tab >> select Visual Basic.

We can also type ALT+F11 to open the VBA editor window.

how to save multiple tabs in excel as separate files

Next, a VBA editor window will open.

  • Then, we will go to the Insert tab >> select Module.

  • After that, we will type the following code.
Sub Save_Tab_into_MultipleFiles()

Dim Multiple_FilePath As String

Multiple_FilePath = Application.ActiveWorkbook.Path
Application.ScreenUpdating = False
Application.DisplayAlerts = False

For Each Sheet In ThisWorkbook.Sheets
Sheet.Copy
Application.ActiveWorkbook.SaveAs 
Filename:=Multiple_FilePath & "\" & Sheet.Name & ".xlsx"
Application.ActiveWorkbook.Close False
Next

Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Code Breakdown

  • First, we created our Sub procedure as Save_Tab_into_MultipleFiles.
  • Next, declared the variable Multiple_FilePath as a String to keep the ActiveWorkbook path.
  • Then, we used a For Loop to separate all available sheets in the workbook.
  • Later, used the Copy method to copy the sheets to a new workbook.
  • Also used the SaveAs method to Save the multiple tabs or sheets in separated Excel files according to the Sheet/Tab name.
  • Then, we will click on the Run button, marked with red color box or we can press F5 key to run the code.

Finally, we can see the folder where our original Excel file also contains all the separated tabs.

Read More: Excel VBA to Save File with Variable Name (5 Examples)


Similar Readings


Method-4: Excel VBA to Separate Multiple Tabs in a Specific Folder

In this method, we will use VBA code to Save Multiple Tabs in Excel as Separate File. Using VBA code, we will create a folder for the separated tabs, and all the sheets will be together in this folder.

Steps:

  • First, we will follow the steps described in Method-3.
  • After that, we will open our Workbook >> go to the Developer tab >> select Visual Basic.

We can also type ALT+F11 to open the VBA editor window.

Next, a VBA editor window will open.

  • Then, we will go to the Insert tab >> select Module.

how to save multiple tabs in excel as separate files

  • After that, we will type the following code.
Sub Save_Multiple_Tab_Specefic_Folder()

Dim File_Extension As String
Dim Format_File As Long
Dim x_worksheet As Worksheet
Dim x_workbook As Workbook
Dim x_New_workbook As Workbook
Dim Folder_Name As String
Application.ScreenUpdating = False
Set x_workbook = Application.ThisWorkbook

DateString = Format(Now, "dd-mm-yyyy hh-mm")
Folder_Name = x_workbook.Path & "\" & x_workbook.Name & " " & DateString

If Val(Application.Version) < 12 Then
File_Extension = ".xls": Format_File = -4143

Else
Select Case x_workbook.FileFormat
Case 51:
File_Extension = ".xlsx": Format_File = 51
Case 52:
If Application.ActiveWorkbook.HasVBProject Then
File_Extension = ".xlsm": Format_File = 52
Else
File_Extension = ".xlsx": Format_File = 51
End If
Case 56:
File_Extension = ".xls": Format_File = 56
Case Else:
File_Extension = ".xlsb": Format_File = 50
End Select
End If

MkDir Folder_Name
For Each x_worksheet In x_workbook.Worksheets
On Error GoTo NErro
If x_worksheet.Visible = xlSheetVisible Then
x_worksheet.Select
x_worksheet.Copy
xFile = Folder_Name & "\" & x_worksheet.Name & File_Extension
Set x_New_workbook = Application.Workbooks.Item(Application.Workbooks.Count)
x_New_workbook.SaveAs xFile, FileFormat:=Format_File
x_New_workbook.Close False, xFile
End If

NErro:
x_workbook.Activate

Next
MsgBox "You can find the files in " & Folder_Name
Application.ScreenUpdating = True
End Sub

Code Breakdown

  • First, we created our Sub procedure as Save_Multiple_Tab_Specefic_Folder.
  • Next, we declared Multiple variables.
  • Afterward, we used the VBA Format Function to create DateString.
  • Later, we used the  Case statement for each specific type of Excel file extension within the If Else statement to check the File Format number.
  • After that, we used Mkdir to create a folder with the Folder_Name where the new sheets will be created, and this Folder_Name will be the same as the original Excel file where the Date and time will be added.
  • Then, we used a For Loop to separate all available sheets in the workbook and keep them in the created New_Folder.
  • Then, we will click on the Run button, marked with red color box or we can press F5 key to run the code.

Finally, we can see the folder where our original Excel file also contains a folder with all the separated tabs.

  • Now, if we click on the folder, we will see all the separated Excel sheets.

how to save multiple tabs in excel as separate files

Read More: Excel VBA Save as File Format (12 Suitable Examples)


Method-5: Using VBA to Separate Worksheet Containing Specific Word

In many cases, we do not need to save multiple tabs in Excel as separate files rather we want to separate one specific sheet. In this method, we want to separate the May-2022 sheet into a folder. We will use the VBA code to do the task.

Steps:

  • First, we will follow the steps described in Method-3.
  • After that, we will open our Workbook >> go to the Developer tab >> select Visual Basic.

We can also type ALT+F11 to open the VBA editor window.

Next, a VBA editor window will open.

  • Then, we will go to the Insert tab >> select Module.

  • After that, we will type the following code.
Sub Split_Sheet_Specific_Word()

Dim Multiple_FilePath As String
Dim Separate As String

Separate = "May-2022"
Multiple_FilePath = Application.ActiveWorkbook.Path
Application.ScreenUpdating = False
Application.DisplayAlerts = False

For Each Sheet In ThisWorkbook.Sheets
If InStr(1, Sheet.Name, Separate, vbBinaryCompare) <> 0 Then
Sheet.Copy
Application.ActiveWorkbook.SaveAs _
Filename:=Multiple_FilePath & "\" & Sheet.Name & ".xlsx"
Application.ActiveWorkbook.Close False
End If
Next

Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Code Breakdown

  • First, we created our Sub procedure as Split_Sheet_Specific_Word.
  • Next, we declared the variable Multiple_FilePath as a String to keep the ActiveWorkbook path, and Separate as a String to keep the Specific File Name.
  • In the Separate variable kept the sheet name “May-2022”, which we want to separate.
  • Then, we used a For Loop to separate the specific sheet of the Excel file.
  • Later, we used the Copy method to copy the sheet to a new workbook.
  • Also used the SaveAs method to Save the sheet in separated Excel files according to the Sheet/Tab name.
  • Then, we will click on the Run button, marked with a red color box or we can press F5 key to run the code.

Finally, we can see the folder where our original Excel file also contains the May-2022 sheet.

how to save multiple tabs in excel as separate files

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


Conclusion

Here, we tried to show you 5 methods to save multiple tabs as separate files in Excel. Thank you for reading this article, we hope this was helpful. If you have any queries or suggestions, please let us know in the comment section. Please visit our website Exceldemy to explore more.


Related Articles

Afia

Afia

Hello, I am Afia Aziz Kona. I graduated in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology (BUET). I have an immense interest in technical writing and content development, therefore, I am working as a content developer at Exceldemy. In my spare time, I travel, watch movies, and cook different dishes.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo