Excel VBA: Split Sheet into Multiple Sheets Based on Rows

In Microsoft Excel, a VBA code can save a lot of time and reduce stress from hectic processes. With a simple code, you can do a lot of operations in Excel. Now, you may have faced a problem that you need to split a worksheet into multiple sheets based on rows. You have come to the right place. In this tutorial, you will learn to split a sheet into multiple sheets based on rows using VBA in Excel.

Split Excel Sheet into Multiple Sheets Based on Rows Using VBA: Step-by-Step Procedure

In the following sections, we are going to show you how to split an Excel sheet into multiple sheets based on rows using VBA. We will break down each portion of the VBA code. So, don’t worry. You can learn a lot about the process if you read the whole article.

To demonstrate this tutorial, we will use this dataset:

Here, you can see a dataset of some products. We will use VBA in Excel to split the data into multiple sheets based on rows. Now, look at the name of the worksheet. Here, “Main” is our master worksheet. We will run our VBA code on this sheet.

We recommend you learn and apply all these instructions

1. Build the Code

Before we start to write the VBA code, we have to insert a module. First, press Alt+F11 on your keyboard to open the VBA editor. Then, select Insert > Module.

Now, it’s time to build the code. Follow these steps and read the breakdown to understand the code better.

Step 1: Create Sub-Procedure

Sub Split_Sheet_based_on_rows()

End Sub

This is our sub-procedure. We will write all the VBA codes here.

Step 2: Declare Necessary Variables

Sub Split_Sheet_based_on_rows()

Dim data_range As Range
Dim start_row As Range
Dim split_row As Integer
Dim main_sheet As Worksheet

End Sub

These will be our variables.

data_range: It will store the dataset range.

start_row: From which row we will start to split.

split_row: Number of rows that we want to split.

main_sheet: It will store the “Main” worksheet.

Step 3: Taking User Inputs for Dataset and Number of Rows to Split

Sub Split_Sheet_based_on_rows()

Dim data_range As Range
Dim start_row As Range
Dim split_row As Integer
Dim main_sheet As Worksheet

xTitleId = "Multiple Sheets Based on Rows"
Set data_range = Application.Selection
Set data_range = Application.InputBox("Data Range", xTitleId, data_range.Address, Type:=8)
split_row = Application.InputBox("Row Number Split", xTitleId, 5, Type:=1)

End Sub

xTitleId: The title we will show in our input box.

Application.InputBox(“Data Range”, xTitleId, data_range.Address, Type:=8): By this line of code, we are taking the dataset range.

Application.InputBox(“Row Number Split”, xTitleId, 5, Type:=1): This line will take the number of rows from the user by which they want to split the sheet into multiple worksheets.

Step 4: Set the Main Sheet and Staring Row to Split

Sub Split_Sheet_based_on_rows()

Dim data_range As Range
Dim start_row As Range
Dim split_row As Integer
Dim main_sheet As Worksheet

xTitleId = "Multiple Sheets Based on Rows"
Set data_range = Application.Selection
Set data_range = Application.InputBox("Data Range", xTitleId, data_range.Address, Type:=8)
split_row = Application.InputBox("Row Number Split", xTitleId, 5, Type:=1)

Set main_sheet = data_range.Parent
Set start_row = data_range.Rows(1)

End Sub

Set main_sheet = data_range.Parent: By this line of code, we are actually storing the “Main” worksheet in the main_sheet variable. “The parent of a range is a worksheet object.”

Set start_row = data_range.Rows(1): It will store the first row from the dataset. Because we will start to split our worksheet into multiple sheets from that row.

Step 5: Create a Loop

Sub Split_Sheet_based_on_rows()

Dim data_range As Range
Dim start_row As Range
Dim split_row As Integer
Dim main_sheet As Worksheet

xTitleId = "Multiple Sheets Based on Rows"
Set data_range = Application.Selection
Set data_range = Application.InputBox("Data Range", xTitleId, data_range.Address, Type:=8)
split_row = Application.InputBox("Row Number Split", xTitleId, 5, Type:=1)

Set main_sheet = data_range.Parent
Set start_row = data_range.Rows(1)

Application.ScreenUpdating = False
For i = 1 To data_range.Rows.Count Step split_row

Next

End Sub

Here, we have created a For-Next loop to loop through the whole dataset. You can see, we have also added an extra “Step”.

We employ the Step keyword to set a different increment for the counter variable of a loop in Excel VBA.

Suppose, you have a code like this:

Dim j As Integer

For j = 1 To 6 Step 2
    Cells(j, 1).Value = 100
Next j

Here, for the first execution, the value of j will be 1. As usual, it will do its task. But, after that, when it returns to the loop, the value of j will be 3, not 2. Because Step 2 means increasing the value of j by 2. So, 1+2 = 3 in this case.

Step 6: Check Whether Your Given Rows are Larger than the Dataset or Not

Sub Split_Sheet_based_on_rows()

Dim data_range As Range
Dim start_row As Range
Dim split_row As Integer
Dim main_sheet As Worksheet

xTitleId = "Multiple Sheets Based on Rows"
Set data_range = Application.Selection
Set data_range = Application.InputBox("Data Range", xTitleId, data_range.Address, Type:=8)
split_row = Application.InputBox("Row Number Split", xTitleId, 5, Type:=1)

Set main_sheet = data_range.Parent
Set start_row = data_range.Rows(1)

Application.ScreenUpdating = False
For i = 1 To data_range.Rows.Count Step split_row
resizeCount = split_row
If data_range.Rows.Count < split_row Then resizeCount = data_range.Rows.Count - 1
Next

End Sub

resizeCount = split_row: Here, we are storing the number of rows to split in another variable.

If data_range.Rows.Count < split_row: Checking whether your given rows are larger than your dataset or not. Suppose, you have a dataset of 5 rows but you want to split them based on 6 rows. It will cause an error.

Then resizeCount = data_range.Rows.Count – 1: If that’s really the case, it will resize your number of rows. You have a dataset of 5 rows but you want to split them based on 6 rows. In this case, your new split rows will be 5. It will help you split the data into two sheets. The first sheet will have 5 rows and the second one have 1 row.

Step 7: Copy the Data

Sub Split_Sheet_based_on_rows()

Dim data_range As Range
Dim start_row As Range
Dim split_row As Integer
Dim main_sheet As Worksheet

xTitleId = "Multiple Sheets Based on Rows"
Set data_range = Application.Selection
Set data_range = Application.InputBox("Data Range", xTitleId, data_range.Address, Type:=8)
split_row = Application.InputBox("Row Number Split", xTitleId, 5, Type:=1)

Set main_sheet = data_range.Parent
Set start_row = data_range.Rows(1)

Application.ScreenUpdating = False
For i = 1 To data_range.Rows.Count Step split_row
resizeCount = split_row
If data_range.Rows.Count < split_row Then resizeCount = data_range.Rows.Count - 1

start_row.Resize(resizeCount).Copy

Next

End Sub

Now, it’s time to copy the data from the sheet.

First, it will start from the first row of your given range. Then, it will expand based on your split rows. Suppose, you want to split the sheet based on 2 rows, and your dataset starts from the range B5:G5.

As you want to split the sheet based on 2 rows, it will expand and select 2 rows and copy them from your dataset.

Step 8: Create Another Worksheet

Sub Split_Sheet_based_on_rows()

Dim data_range As Range
Dim start_row As Range
Dim split_row As Integer
Dim main_sheet As Worksheet

xTitleId = "Multiple Sheets Based on Rows"
Set data_range = Application.Selection
Set data_range = Application.InputBox("Data Range", xTitleId, data_range.Address, Type:=8)
split_row = Application.InputBox("Row Number Split", xTitleId, 5, Type:=1)

Set main_sheet = data_range.Parent
Set start_row = data_range.Rows(1)

Application.ScreenUpdating = False
For i = 1 To data_range.Rows.Count Step split_row
resizeCount = split_row
If data_range.Rows.Count < split_row Then resizeCount = data_range.Rows.Count - 1
start_row.Resize(resizeCount).Copy

Application.Worksheets.Add after:=Application.Worksheets(Application.Worksheets.Count)

Next

End Sub

Now, we have to paste the data onto another sheet. For that reason, we are adding a sheet to paste the data. As we are using the loop, it will create multiple sheets in later execution.

Step 9: Paste the Data

Sub Split_Sheet_based_on_rows()

Dim data_range As Range
Dim start_row As Range
Dim split_row As Integer
Dim main_sheet As Worksheet

xTitleId = "Multiple Sheets Based on Rows"
Set data_range = Application.Selection
Set data_range = Application.InputBox("Data Range", xTitleId, data_range.Address, Type:=8)
split_row = Application.InputBox("Row Number Split", xTitleId, 5, Type:=1)

Set main_sheet = data_range.Parent
Set start_row = data_range.Rows(1)

Application.ScreenUpdating = False
For i = 1 To data_range.Rows.Count Step split_row
resizeCount = split_row
If data_range.Rows.Count < split_row Then resizeCount = data_range.Rows.Count - 1
start_row.Resize(resizeCount).Copy
Application.Worksheets.Add after:=Application.Worksheets(Application.Worksheets.Count)

Application.ActiveSheet.Range("A1").PasteSpecial

Next

End Sub

Now, in that newly created sheet, we will paste the data.

Step 10: Select Next Number of Rows

Sub Split_Sheet_based_on_rows()

Dim data_range As Range
Dim start_row As Range
Dim split_row As Integer
Dim main_sheet As Worksheet

xTitleId = "Multiple Sheets Based on Rows"
Set data_range = Application.Selection
Set data_range = Application.InputBox("Data Range", xTitleId, data_range.Address, Type:=8)
split_row = Application.InputBox("Row Number Split", xTitleId, 5, Type:=1)

Set main_sheet = data_range.Parent
Set start_row = data_range.Rows(1)

Application.ScreenUpdating = False
For i = 1 To data_range.Rows.Count Step split_row
resizeCount = split_row
If data_range.Rows.Count < split_row Then resizeCount = data_range.Rows.Count - 1
start_row.Resize(resizeCount).Copy
Application.Worksheets.Add after:=Application.Worksheets(Application.Worksheets.Count)
Application.ActiveSheet.Range("A1").PasteSpecial

Set start_row = start_row.Offset(split_row)

Next

End Sub

We have pasted the data into a new worksheet. Now, we will set a new row to start again this process. If you want to split the sheet based on 2 rows, it will split the first 2 rows. After completing all the processes from above, it will start from the third row.

When it goes back to the loop, it will work with the next 2 rows.

Step 11: Clear the Clipboard (Final Code)

Sub Split_Sheet_based_on_rows()

Dim data_range As Range
Dim start_row As Range
Dim split_row As Integer
Dim main_sheet As Worksheet

xTitleId = "Multiple Sheets Based on Rows"
Set data_range = Application.Selection
Set data_range = Application.InputBox("Data Range", xTitleId, data_range.Address, Type:=8)
split_row = Application.InputBox("Row Number Split", xTitleId, 5, Type:=1)

Set main_sheet = data_range.Parent
Set start_row = data_range.Rows(1)

Application.ScreenUpdating = False
For i = 1 To data_range.Rows.Count Step split_row
resizeCount = split_row
If data_range.Rows.Count < split_row Then resizeCount = data_range.Rows.Count - 1
start_row.Resize(resizeCount).Copy
Application.Worksheets.Add after:=Application.Worksheets(Application.Worksheets.Count)
Application.ActiveSheet.Range("A1").PasteSpecial
Set start_row = start_row.Offset(split_row)
Next

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

Application.CutCopyMode = False: When we cut or copy something, it is stored to the clipboard and it can be pasted anywhere. We are setting this property to False to clear our clipboard.

2. Run the Code

It’s time to run the code and split our Excel sheet into multiple sheets. Follow these steps.

📌 Steps

First, press Alt+F8 on your keyboard to open the Macro dialog box.
Now, select Split_Sheet_based_on_rows and click on Run.

Now, select your dataset range. Click on OK.

After that, enter the number of rows by which you want to split your data into multiple Excel sheets.
Finally, click on OK.

Here, you can see that we have new sheets in our workbook and we also have our data. Now, we wanted to split the sheet into multiple sheets based on 2 rows here. For that reason, each sheet has 2 rows in it.

So, we can say, our VBA worked successfully to split the Excel sheet into multiple sheets based on rows. Give this a try and try this with the new data range and number of rows.

💬 Things to Remember

✎ We didn’t have any error handling code in it. So, if you do not give a valid range or cancel before you give any input, it will show an error. So, try to give values in the input box.

Download Practice Workbook

Split Sheet into Multiple Sheets Based on Rows.xlsm

Conclusion

To conclude, I hope this tutorial has provided you with a piece of useful knowledge to split an Excel sheet into multiple sheets based on rows using VBA codes. We recommend you learn and apply all these instructions to your dataset. Download the practice workbook and try these yourself. Also, feel free to give feedback in the comment section.

<< Go Back to Split Excel Cell | Excel Worksheets | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!