3 Quick Ways to Split Data Into Multiple Worksheets

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.

Dataset of Split Data Into Multiple Worksheets.

method

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.

Use of Filter feature to split data into multiple worksheets.

  • Now, click to get the Context menu from drop-down box.
  • Then, only select Banana and hit the OK button.

Context menu to split dataset based on products.

  • Therefore, data for Banana appears only.

Filtering the dataset.

  • Now, copy them to another worksheet.

Dataset split based on product.

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

Dataset split based on another product.

  • However, data containing Mango showed in another Worksheet.

Split data based on product.

method

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.

Enabling VBA.

  • Click on Developer and click on OK button.

Getting Developer Menu on the Top Ribbon.

  • Now Developer mode is on.
  • Then navigate the Visual Basic option.
  • To insert the VBA code, click on Module from the Insert menu.

Insert VBA code in the module.

  • 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

VBA to split data into multiple worksheets.

  • Then, input the dataset range $B$6:$F$16 with repeated value and hit the OK button.

Choosing range.

  • Next, Insert the Split Row Num cell and hit the OK button.

Row number to split.

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

Data is split into two worksheets.

  • On the other hand, the Rest of the rows are shown in Sheet 11.

Another worksheet contains split data.

method

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.

dataset to split into multiple workbooks.

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

Data split into three workbooks.


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

VBA to convert worksheets into different workbooks.

  • Finally, executing the VBA code, we get the .xlsx files separately for each worksheet.

Conversion of Workbooks from Worksheets.


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

VBA to convert worksheets into workbooks.

  • Finally, executing the VBA code, we get the .pdf files separately for each worksheet.

Conversion of PDF files from Worksheets.


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

(Visited 38 times, 1 visits today)
MD Tanvir Rahman

MD Tanvir Rahman

Hello, I am Tanvir. Graduated from BUET. Had a year-long Experience at Operation of Vitacan Industries Ltd. I found Excel is the most unique software to reduce both time and paper. Now, I am an Excel enthusiast, love to play with data and publish content.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo