Afia Kona
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.
Save Multiple Tabs in Excel as Separate Files: 5 Methods
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.
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.
- 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.
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.
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.
Finally, we can see that February-2022 is now in Book3 Workbook.
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.
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 a red color box or we can press the F5 key to run the code.
Finally, we can see the folder where our original Excel file also contains all the separated tabs.
Similar Readings
- How to Save Excel File in XLSX Format
- How to Save Excel File as CSV
- How to Save Multiple Sheets in Excel to CSV
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.
- 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.
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.
Download Practice Workbook
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.
Related Articles
- How to Save a Worksheet in Excel
- How to Save a Worksheet in Excel to a Separate File
- How to Save a Copy of an Excel File
- How to Save Excel as PDF
- How to Save One Sheet in Excel as PDF
- How to Save Multiple Excel Sheets as One File
- How to Save Multiple Sheets in Excel
- How to Save Excel File with Password
- [Fixed!] Why Is Excel Not Saving My Formatting?
- [Fixed!] Document Not Saved Excel Network Drive
- [Fixed!] Excel CSV File Not Saving Changes
