Split Data into Multiple Worksheets with Set of Rows using Macros (VBA) in Excel!!
Tags: vbamacros, macrosvba
Published: 2021-03-05 08:32:35

---
title: Split Data into Multiple Worksheets with Set of Rows using Macros (VBA) in Excel!!
published: true
date: 2021-03-05 06:49:40 UTC
tags: VBAMacros,MacrosVBA
canonical_url: https://geekexcel.com/split-data-into-multiple-worksheets-with-set-of-rows-using-macros-vba-in-excel/
---

In this tutorial, we will guide you to know the simple instructions to **split data into multiple worksheets with a set of rows using Macros ([VBA](https://geekexcel.com/excel-office-365-vba-overview-definition-and-examples/))** in Excel Office 365. With the help of macros, we can easily split data into multiple worksheets based on row count. Let’s get into this article.
## Split Data into Multiple Worksheets by Row Count:

Kindly, follow the below instructions to split data into multiple sheets in Excel.

- Assuming that, we have a **list of data with 6 rows** , and we want to split them into multiple worksheets by row count of every two data.

![Input range](https://geekexcel.com/wp-content/uploads/2021/03/EXCEL_CyjRky5TqH.png)<figcaption id="caption-attachment-22232">Input range</figcaption>

- On the **Developer Tab, select** the **Visual Basic** option under the **Code** section.

![Select Visual Basic option](https://geekexcel.com/wp-content/uploads/2020/09/Select-Visual-Basic-option-4.png)<figcaption id="caption-attachment-14755">Select the Visual Basic option</figcaption>

- Then you need to copy and paste the code given below.

VBA code to split data using Macros

Sub SplitWorksheet()

Dim lngLastRow As Long

Dim lngNumberOfRows As Long

Dim lngI As Long

Dim strMainSheetName As String

Dim currSheet As Worksheet

Dim prevSheet As Worksheet

‘Number of rows to split among worksheets

lngNumberOfRows = 2

‘Current worksheet in workbook

Set prevSheet = ThisWorkbook.ActiveSheet

‘First worksheet name

strMainSheetName = prevSheet.Name

‘Number of rows in worksheet

lngLastRow = prevSheet.Cells(Rows.Count, 1).End(xlUp).Row

‘Worksheet counter for added worksheets

lngI = 1

While lngLastRow > lngNumberOfRows

Set currSheet = ThisWorkbook.Worksheets.Add

With currSheet

.Move after:=Worksheets(Worksheets.Count)

.Name = strMainSheetName + “(” + CStr(lngI) + “)”

End With

With prevSheet.Rows(lngNumberOfRows + 1 & “:” & lngLastRow).EntireRow

.Cut currSheet.Range(“A1”)

End With

lngLastRow = currSheet.Cells(Rows.Count, 1).End(xlUp).Row

Set prevSheet = currSheet

lngI = lngI + 1

Wend

End Sub

- You need to **save the code** by selecting it. Then **close** the **window.**

![Save the code](https://geekexcel.com/wp-content/uploads/2021/03/EXCEL_JcdG2x2U31.png)<figcaption id="caption-attachment-22233">Save the code</figcaption>

- You have to open the sheet containing the data. On the **Developer Tab, choose** the **Macros option** in the **Code** section.

![Choose the Macros option](https://geekexcel.com/wp-content/uploads/2020/09/Choose-the-Macros-option-2.png)<figcaption id="caption-attachment-14747">Choose the Macros option</figcaption>

- You need to make sure that your macro is selected and **click** the **Run** button.

![Click the Run button](https://geekexcel.com/wp-content/uploads/2021/03/EXCEL_bhBdrmDkbg.png)<figcaption id="caption-attachment-22234">Click the Run button</figcaption>

- Finally, you will see the result as shown in the below image.

![Output](https://geekexcel.com/wp-content/uploads/2021/03/EXCEL_2UYyOSb3JD.png)<figcaption id="caption-attachment-22235">Output</figcaption>

## Verdict:

In this tutorial, we guided you to know the steps to **split data into multiple worksheets with a set of rows using Macros ([VBA](https://geekexcel.com/excel-office-365-vba-overview-definition-and-examples/))** in Excel Office 365. Share your **feedback** in the below comment section. Thanks for visiting **[Geek Excel.](https://geekexcel.com/)** Keep Learning!

#### Read Also:

- [How to Compare Two Ranges using Macros (VBA) in Excel 365?](https://geekexcel.com/how-to-compare-two-ranges-using-macros-vba-in-excel-365/)
- [Create Folders Based on Cell Values using Macros (VBA) in Excel 365!!](https://geekexcel.com/create-folders-based-on-cell-values-using-macros-vba-in-excel-365/)