Copy Each Excel Tab To Individual File or PDF (In Seconds!)
Splitting Up Your Excel Sheets
If you’ve come across this article, chances are you are looking for a solution that will allow you to quickly and easily split up your Excel file into multiple files. If you are manually doing this, you’re probably spending hours on a recurring basis copying each spreadsheet tab into a new workbook file and saving it to the proper location.
In this post, I will provide you with a few examples of how you can automate this task with VBA code. If you have never used VBA code before, you should check out my article How To Use A VBA Macro You Found On The Internet. This will provide you with the straightforward and non-techy steps to use the code that is in this article.
In each of my code examples, I will provide you with ways to save the new files in an Excel format or as a PDF file (or you can do both if you want). The file name will match the name of the Excel tab and will be saved in the same folder where your source Excel file is located.
VBA Code: Copy All (Visible) Tabs to Individual Files
In this example VBA code, all visible spreadsheet tabs will be copied to both a new Excel file and also saved as a PDF. You can remove either section of the code if you wish to only save as PDF or only save as a new Excel file.
Sub CopyTabsToIndividualFiles()
'PURPOSE: Copy all visible tabs to individual files
'SOURCE: www.TheSpreadsheetGuru.com
Dim FolderPath As String
Dim sht As Worksheet
'Determine Folder Path
FolderPath = Application.ActiveWorkbook.Path
'Optimize Code (Turn off)
Application.ScreenUpdating = False
Application.DisplayAlerts = False
'Loop through each sheet in ActiveWorkbook
For Each sht In ThisWorkbook.Worksheets
'Ensure sheet is visible so we can copy it
If sht.Visible = xlSheetVisible Then
'Copy Sheet to new workbook
sht.Copy
DoEvents
'Save tab as an Excel File (use Sheet's name)
Application.ActiveWorkbook.SaveAs Filename:=FolderPath & "\" & sht.Name & ".xlsx"
'Save tab as a PDF File (use Sheet's name)
Application.ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=FolderPath & "\" & sht.Name & ".pdf"
'Close File
Application.ActiveWorkbook.Close False
End If
Next
'Optimize Code (Turn back on)
Application.DisplayAlerts = True
Application.ScreenUpdating = True
'Notify user all copies have been made
MsgBox "All sheets have been saved to individual files"
End SubVBA Code: Copy All Selected Tabs to Individual Files
This example VBA macro code is very similar to the prior code example, however, it adds the ability to only export the worksheets that you have selected. This is handy if you only wish to export and split out a subset of your Excel tabs.
Sub CopySelectedTabsToIndividualFiles()
'PURPOSE: Copy all selected tabs to individual files
'SOURCE: www.TheSpreadsheetGuru.com
Dim FolderPath As String
Dim sht As Object
'Determine Folder Path
FolderPath = Application.ActiveWorkbook.Path
'Optimize Code (Turn off)
Application.ScreenUpdating = False
Application.DisplayAlerts = False
'Loop through selected sheets in ActiveWorkbook
For Each sht In ActiveWindow.SelectedSheets
'Copy Sheet to new workbook
sht.Copy
DoEvents
'Save tab as an Excel File (use Sheet's name)
Application.ActiveWorkbook.SaveAs Filename:=FolderPath & "\" & sht.Name & ".xlsx"
'Save tab as a PDF File (use Sheet's name)
Application.ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=FolderPath & "\" & sht.Name & ".pdf"
'Close File
Application.ActiveWorkbook.Close False
Next
'Optimize Code (Turn back on)
Application.DisplayAlerts = True
Application.ScreenUpdating = True
'Notify user all copies have been made
MsgBox "All selected sheets have been saved to individual files"
End SubVBA Code: Copy All Blue Sheets to Individual Files
If you would like to export only spreadsheet tabs that are a particular color, you can use the below code example. All you need to do is set the TabColor variable to the RGB color code your choosing.
The below macro code has a test built into it that only proceeds to copy the tab if its tab color matches the color you are looking for.
Sub CopyColoredTabsToIndividualFiles()
'PURPOSE: Copy all visible tabs of a specific color to individual files
'SOURCE: www.TheSpreadsheetGuru.com
Dim FolderPath As String
Dim sht As Worksheet
Dim TabColor As Long
'Indentify Tab Color
TabColor = RGB(0, 112, 192)
'Determine Folder Path
FolderPath = Application.ActiveWorkbook.Path
'Optimize Code (Turn off)
Application.ScreenUpdating = False
Application.DisplayAlerts = False
'Loop through each sheet in ActiveWorkbook
For Each sht In ThisWorkbook.Worksheets
'Ensure sheet is visible so we can copy it
If sht.Visible = xlSheetVisible And sht.Tab.Color = TabColor Then
'Copy Sheet to new workbook
sht.Copy
DoEvents
'Save tab as an Excel File (use Sheet's name)
Application.ActiveWorkbook.SaveAs Filename:=FolderPath & "\" & sht.Name & ".xlsx"
'Save tab as a PDF File (use Sheet's name)
Application.ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=FolderPath & "\" & sht.Name & ".pdf"
'Close File
Application.ActiveWorkbook.Close False
End If
Next
'Optimize Code (Turn back on)
Application.DisplayAlerts = True
Application.ScreenUpdating = True
'Notify user all copies have been made
MsgBox "All colored sheets have been saved to individual files"
End SubVBA Code: Copy Specific Sheets to Individual Files
If you only want to copy specific sheet names into individual files, you can use the following VBA code example.
This macro uses the variable SheetList which will allow you to write out each sheet name you are wanting to isolate. Make sure you add a semi-colon (;) immediately after each tab name as code logic uses the semi-colon symbol separate out the names when it looks for a match.
Sub CopySpecificTabsToIndividualFiles()
'PURPOSE: Copy specific visible tabs to individual files
'SOURCE: www.TheSpreadsheetGuru.com
Dim FolderPath As String
Dim sht As Worksheet
Dim SheetList As String
'List Sheet Names separated with semi-colon
SheetList = "Sheet1; Sheet2; Instructions; My Report;"
'Determine Folder Path
FolderPath = Application.ActiveWorkbook.Path
'Optimize Code (Turn off)
Application.ScreenUpdating = False
Application.DisplayAlerts = False
'Loop through each sheet in ActiveWorkbook
For Each sht In ThisWorkbook.Worksheets
'Verify Sheet Name
If InStr(1, SheetList, sht.Name & ";") > 0 Then
'Ensure sheet is visible so we can copy it
If sht.Visible = xlSheetVisible Then
'Copy Sheet to new workbook
sht.Copy
DoEvents
'Save tab as an Excel File (use Sheet's name)
Application.ActiveWorkbook.SaveAs Filename:=FolderPath & "\" & sht.Name & ".xlsx"
'Save tab as a PDF File (use Sheet's name)
Application.ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=FolderPath & "\" & sht.Name & ".pdf"
'Close File
Application.ActiveWorkbook.Close False
End If
End If
Next
'Optimize Code (Turn back on)
Application.DisplayAlerts = True
Application.ScreenUpdating = True
'Notify user all copies have been made
MsgBox "All desired sheets have been saved to individual files"
End SubI Hope This Helped!
Hopefully, I was able to explain how you can use VBA code to automate splitting out your Excel tabs into new files or PDF files. If you have any questions about this technique or suggestions on how to improve it, please let me know in the comments section below.
After 10+ years of creating macros and developing add-ins, I've compiled all the hacks I wish I had known years ago!
Keep Learning
Chris Newman
Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.