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 SubThis 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 SubThese 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 SubxTitleId: 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 SubSet 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 SubHere, 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 jHere, 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 SubresizeCount = 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 SubNow, 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 SubNow, 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 SubNow, 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 SubWe 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 SubApplication.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.
Read More: How to Split Excel Sheet into Multiple Sheets Based on Column Value
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.
Read More: How to Split Excel Sheet into Multiple Worksheets
💬 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
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.
Related Articles
- How to Split Screen in Excel
- [Fix:] Excel View Side by Side Not Working
- How to Split Sheet into Multiple Sheets Based on Rows in Excel
- How to Split Excel Sheet into Multiple Files
- How to Enable Side-by-Side View with Vertical Alignments in Excel
- How to View Excel Sheets in Separate Windows
- How to Split a Workbook to Separate Excel Files with VBA Code
