Suppose you have a worksheet of large data that contains Name, Region, Product, ID, etc. In this case, to get data based on a particular region or Product, there is no other option rather than split data into multiple worksheets. It is safest to Split data into multiple worksheets to handle large data. In this article, we are going to show you several approaches to splitting data into multiple worksheets.
📁 Download Excel File
To practice please download the excel file from here:
Learn to Split Data into Separate Worksheets with These 3 Ways
To split data range into multiple worksheets, we considered a data set named Sales Data of Amigo Super Store- 2022 containing Seller, Products, selling quantity, and amount each month. The dataset also has 16 rows and 6 columns. To split data into multiple worksheets and workbooks we are going to use the Filter option from Top Ribbon and VBA Macro. Let’s get started.
1. Applying Filter Feature to Split into Distinct Worksheets
To split into multiple worksheets, one may use the Filter option to filter out the data based on certain criteria and copy them to another worksheet. Please follow the steps.
⬇️⬇️ STEPS ⬇️⬇️
- Initially, select the entire table.
- Secondly, navigate the Sort and Filter feature from the Data menu.
- Thirdly, select the Filter feature.
- Now, click to get the Context menu from drop-down box.
- Then, only select Banana and hit the OK button.
- Therefore, data for Banana appears only.
- Now, copy them to another worksheet.
- Similarly selecting Orange and Mango separately from the Context Menu, you will get data for Orange and Mango.
- Copying them to another worksheet, you will be able to get data for each product separately.
- However, data containing Mango showed in another Worksheet.
2. Split Data into Excel Sheets Counting Rows Utilizing VBA
Using VBA code we can split into multiple worksheets in Excel based on row. With the help of the Developer option, you can generate a program on your own. Please follow the steps below.
⬇️⬇️ STEPS ⬇️⬇️
- Check first if your Developer option is available or not.
- To enable the Developer option, Right-Click on mouse at the Top Ribbon and select Customize the Ribbon.
- Click on Developer and click on OK button.
- Now Developer mode is on.
- Then navigate the Visual Basic option.
- To insert the VBA code, click on Module from the Insert menu.
- Now insert the code in VBA module and tap the Run icon.
Code
Sub SplitExcelSheet_into_MultipleSheets()
Dim Workrange As Range
Dim x_Row As Range
Dim split_row As Integer
Dim x_Ws As Worksheet
On Error Resume Next
EcelTitleId = "Split Row Numt"
Set Workrange = Application.Selection
Set Workrange = Application.InputBox("Range", ExcelTitleId, Workrange.Address, Type:=8)
split_row = Application.InputBox("Split Row Num", ExcelTitleId, 4, Type:=1)
Set x_Ws = Workrange.Parent
Set x_Row = Workrange.Rows(1)
Application.ScreenUpdating = False
For j = 1 To Workrange.Rows.Count Step split_row
resizeCount = split_row
If (Workrange.Rows.Count - x_Row.Row + 1) < split_row Then resizeCount = Workrange.Rows.Count - x_Row.Row + 1
x_Row.Resize(resizeCount).Copy
Application.Worksheets.Add after:=Application.Worksheets(Application.Worksheets.Count)
Application.ActiveSheet.Range("A1").PasteSpecial
Set x_Row = x_Row.Offset(split_row)
Next
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
- Then, input the dataset range $B$6:$F$16 with repeated value and hit the OK button.
- Next, Insert the Split Row Num cell and hit the OK button.
- Thus, rows are split after every 6 rows.
- Now, Split rows take place at Sheet 10 and Sheet 11 of the same workbook.
- Therefore, 6 rows are shown in Sheet 10.
- On the other hand, the Rest of the rows are shown in Sheet 11.
3. Break Worksheet into Separate Workbook Based on Column
Similarly, Using VBA code, we can split into multiple workbooks based on columns. With the help of the Developer option, you can generate a program on your own. Please follow the steps below.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly, Here is our dataset containing the A3:E14 range.
- Secondly, following the same steps as the previous method, insert the VBA code and tap the Run option.
Code
Sub SplitSheetIntoMultipleWorkbooks()
Dim obj_worksheet As Excel.Worksheet
Dim n_last_row, n_row, n_next_row As Integer
Dim str_col_value As String
Dim obj_dictionary As Object
Dim var_col_Values As Variant
Dim var_col_Value As Variant
Dim obj_Excel_Workbook As Excel.Workbook
Dim obj_Sheet As Excel.Worksheet
Set obj_worksheet = ActiveSheet
n_last_row = obj_worksheet.Range("A" & obj_worksheet.Rows.Count).End(xlUp).Row
Set obj_dictionary = CreateObject("Scripting.Dictionary")
For n_row = 4 To n_last_row
str_col_value = obj_worksheet.Range("C" & n_row).Value
If obj_dictionary.Exists(str_col_value) = False Then
obj_dictionary.Add str_col_value, 1
End If
Next
var_col_Values = obj_dictionary.Keys
For j = LBound(var_col_Values) To UBound(var_col_Values)
var_col_Value = var_col_Values(i)
Set obj_Excel_Workbook = Excel.Application.Workbooks.Add
Set obj_Sheet = obj_Excel_Workbook.Sheets(1)
obj_Sheet.Name = obj_worksheet.Name
obj_worksheet.Rows(3).EntireRow.Copy
obj_Sheet.Activate
obj_Sheet.Range("A3").Select
obj_Sheet.Paste
For n_row = 4 To n_last_row
If CStr(obj_worksheet.Range("C" & n_row).Value) = CStr(var_col_Value) Then
obj_worksheet.Rows(n_row).EntireRow.Copy
n_next_row = obj_Sheet.Range("A" & obj_worksheet.Rows.Count).End(xlUp).Row + 1
obj_Sheet.Range("A" & n_next_row).Select
obj_Sheet.Paste
obj_Sheet.Columns("A:D").AutoFit
End If
Next
Next
End Sub
- In the end, we get in three different workbooks named Book10, Book11, and Book12.
How to Split Each Excel Sheet into Separate Workbooks
A Workbook containing some worksheets can be converted into separate workbooks. Please follow the process.
⬇️⬇️ STEPS ⬇️⬇️
- Similarly, Following the above process, insert the following code to create separate workbooks for each worksheet.
Code
Sub SplitWorksheet()
Dim each_sheet As String
each_sheet = Application.ActiveWorkbook.Path
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each wbs In ThisWorkbook.Sheets
wbs.Copy
Application.ActiveWorkbook.SaveAs Filename:=each_sheet & "\" & wbs.Name & ".xlsx"
Application.ActiveWorkbook.Close False
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
- Finally, executing the VBA code, we get the .xlsx files separately for each worksheet.
How to Split Each Excel Sheet into Separate PDF Files
A Workbook containing some worksheets can be converted into separate PDF files. Please follow the steps below.
⬇️⬇️ STEPS ⬇️⬇️
- Following the same steps, insert the following code to create separate PDF files for each worksheet.
Code
Sub Split_Worksheet()
Dim Each_sheet As String
Each_sheet = Application.ActiveWorkbook.Path
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each wbs In ThisWorkbook.Sheets
wbs.Copy
Application.ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Each_sheet & "\" & wbs.Name & ".xlsx"
Application.ActiveWorkbook.Close False
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
- Finally, executing the VBA code, we get the .pdf files separately for each worksheet.
📄 Important Notes
🖊️ Enable the Developer menu first to execute VBA code.
🖊️ Change cell reference according to your worksheet.
📝 Takeaway from This Article
📌 use of VBA to split data into multiple workbooks as well as worksheets.
📌 Conversion of Excel Worksheets to separate Workbooks.
📌 Conversion of Excel Worksheets to separate PDF files.
Conclusion
We demonstrated every possible way to split data into multiple workbooks as well as worksheets in Excel. And, I hope you enjoyed your learning. Any suggestions, as well as queries, are appreciated. Also, for a better understanding and new knowledge, don’t forget to visit www.ExcelDen.com.