How to Split Sheet into Multiple Sheets Based on Rows in Excel

Get FREE Advanced Excel Exercises with Solutions!

Sometimes for easy calculation, we need to split an Excel sheet into multiple sheets based on rows. In this article, we are going to learn how to do that by Microsoft Excel VBA code with a beautiful example and explanation.

split excel sheet into multiple sheets based on rows


Download Practice Workbook

Download the following workbook and exercise.


Excel VBA to Split Excel Sheet into Multiple Sheets Based on Rows

VBA (Visual Basic for Application) is one of the most popular programming languages for Microsoft Excel among all. To develop a user-defined function, we can use Excel VBA. It is very easy to learn as well as easy to apply.


Introduction to the Sheet That Has to be Split into Multiple Sheets in Excel

Assuming we have a dataset (B4:C10) of employees with their working hours in Sheet1. We are going to split this sheet into multiple sheets. Each sheet will contain two rows.

Introduction to the Sheet That Has to be Split into Multiple Sheets in Excel


Step 1: Launch the VBA Window to Split an Excel Sheet into Multiple Ones

  • First, select the worksheet from the sheet bar.
  • Next right-click on the sheet.
  • Click on the View Code.

VBA Window to Split an Excel Sheet into Multiple Ones


Step 2: Type and Run the VBA Codes

  • A VBA Module window opens here. We can open it by using the keyboard shortcut ‘ Alt + F11’.
  • Now type the below code:
Sub SplitSheet()
Dim Rng As Range
Dim xRow As Range
Dim SplitRow As Integer
Dim xSheet As Worksheet
On Error Resume Next
xTitleId = "ExcelSplit"
Set Rng = Application.Selection
Set Rng = Application.InputBox("Range", xTitleId, Rng.Address, Type:=8)
SplitRow = Application.InputBox("Row Number Split", xTitleId, 5, Type:=1)
Set xSheet = Rng.Parent
Set xRow = Rng.Rows(1)
Application.ScreenUpdating = False
For i = 1 To Rng.Rows.Count Step SplitRow
resizeCount = SplitRow
If (Rng.Rows.Count - xRow.Row + 1) < SplitRow Then resizeCount = Rng.Rows.Count - xRow.Row + 1
xRow.Resize(resizeCount).Copy
Application.Worksheets.Add after:=Application.Worksheets(Application.Worksheets.Count)
Application.ActiveSheet.Range("A1").PasteSpecial
Set xRow = xRow.Offset(SplitRow)
Next
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
  • Then click on the Run option or we can simply press the F5 key to run the code.

VBA Codes to Split a Sheet into Multiple Ones


Step 3: Name and Run the Macro to Split a Sheet into Multiple Ones Based on Rows

  • A confirmation Macros window pops up.
  • After that, select the sheet name and click on the Run.

Macro to Split a Sheet into Multiple Ones Based on Rows


Step 4: Select Data Range

  • Specify the range of data in the ExcelSplit Range dialogue box and select OK.

Data Range That Will be Split into Multiple Sheets


Step 5: Specify the Number of Rows That Will Be Displayed in Each Sheet

  • Again write down the row number we want to split in ExcelSplit Row Number Split dialogue box and select OK. Here we write ‘2’.

Number of Rows to Split a Sheet into Multiple Ones


Final Output With Multiple Excel Sheets Based on Rows

  • Finally, we can see that the main sheet is split into multiple sheets. Each sheet contains two rows.

Final Output With Multiple Excel Sheets Based on Rows

Read More: How to Split Excel Sheet into Multiple Sheets Based on Column Value


Conclusion

By using this Microsoft Excel VBA code, we can split the Excel sheet into multiple sheets based on rows. There is a practice workbook added. Go ahead and give it a try. Feel free to ask anything or suggest any new methods.


Related Readings

Nuraida Kashmin
Nuraida Kashmin

Hi Everyone! I am Nuraida. Cordially welcome you to my profile. I am a Team Leader of Excel and VBA Content Developer in ExcelDemy. Here I will also post articles related to Microsoft Excel. With a strong educational background in Mechanical Engineering through experimental learning from Rajshahi University of Engineering & Technology. Skilled in Microsoft Word, Creative Writing, Microsoft Excel, Project Management, Support to Organize Different Events, Reporting, Monitoring & Documentation, Online Advocacy and Event Management Related to SAP and Youth Leaders.

4 Comments
  1. Hi Nuraida,
    must say this is just what I was looking for. Was also wondering how to change the sheet names to a cell reference, rather than Sheet1, Sheet2, etc.
    For example, I have a file with 1000 lines of addresses in Germany (DE).
    Splitting them into files of 100, I get from Sheet 1 to Sheet 10. Would be great to have them automatically named, DE1 to DE10.

    • Reply Avatar photo
      Naimul Hasan Arif Mar 9, 2023 at 5:35 PM

      Dear SRECKO SELENDIC,
      Thanks for your appreciation. It means a lot. In order to set sheet names based on cell reference, we can use a For loop along with Worksheets.name property. Here, I have written a code to split sheet and rename sheet split sheet keeping the main sheet name unchanged. Follow the following code to do so.

      Sub SplitSheet()
      Dim Rng As Range
      Dim xRow As Range
      Dim SplitRow As Integer
      Dim xSheet As Worksheet
      On Error Resume Next
      xTitleId = "ExcelSplit"
      Set Rng = Application.Selection
      Set Rng = Application.InputBox("Range", xTitleId, Rng.Address, Type:=8)
      SplitRow = Application.InputBox("Row Number Split", xTitleId, 5, Type:=1)
      Set xSheet = Rng.Parent
      Set xRow = Rng.Rows(1)
      Application.ScreenUpdating = False
      For i = 1 To Rng.Rows.Count Step SplitRow
      resizeCount = SplitRow
      If (Rng.Rows.Count - xRow.Row + 1) < SplitRow Then resizeCount = Rng.Rows.Count - xRow.Row + 1
      xRow.Resize(resizeCount).Copy
      Application.Worksheets.Add after:=Application.Worksheets(Application.Worksheets.Count)
      Application.ActiveSheet.Range("A1").PasteSpecial
      
      For j = 2 To Worksheets.Count
      Worksheets(j).Name = "DE" & j - 1
      Next j
      
      Set xRow = xRow.Offset(SplitRow)
      Next
      Application.CutCopyMode = False
      Application.ScreenUpdating = True
      End Sub 

      The output will be like the following image.

  2. Hi, is there a way to use this exact code but to alter it so instead of splitting based on number of rows, you can split it based on certain row conditions?
    For example, all rows that are green.

    • Dear MADI,
      Thank you for following our article.
      To split into sheets based on certain row conditions, we have to enter an IF condition that checks if the row is of a certain colour. Here, I have added the code to do this.

      Sub SplitSheetByGreenColor()
          Dim Rng As Range
          Dim xRow As Range
          Dim SplitRow As Integer
          Dim xSheet As Worksheet
          Dim i As Integer
          Dim resizeCount As Integer
          
          On Error Resume Next
          xTitleId = "ExcelSplit"
          Set Rng = Application.Selection
          Set Rng = Application.InputBox("Range", xTitleId, Rng.Address, Type:=8)
          SplitRow = Application.InputBox("Row Number Split", xTitleId, 5, Type:=1)
          Set xSheet = Rng.Parent
          Set xRow = Rng.Rows(1)
          Application.ScreenUpdating = False
          
          For i = 1 To Rng.Rows.Count Step SplitRow
              resizeCount = SplitRow
              If (Rng.Rows.Count - xRow.Row + 1) < SplitRow Then resizeCount = Rng.Rows.Count - xRow.Row + 1
              
              If xRow.Cells(1).Interior.Color = RGB(0, 255, 0) Then
                  xRow.Resize(resizeCount).Copy
                  Application.Worksheets.Add after:=Application.Worksheets(Application.Worksheets.Count)
                  Application.ActiveSheet.Range("A1").PasteSpecial
              End If
              
              For j = 2 To Worksheets.Count
              Worksheets(j).Name = "Green" & j - 1
              Next j
      
              Set xRow = xRow.Offset(SplitRow)
          Next
          
          Application.CutCopyMode = False
          Application.ScreenUpdating = True
      End Sub

      This code checks if the interior colour of the first cell in each row of the selected range is Green(RGB(0,255,0)). The entire row is copied and pasted into a new sheet if rows meet the condition. You can alter this colour condition according to your need.

      Regards
      Priti

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo