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) 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.
- On the Developer Tab, select the Visual Basic option under the Code section.
- 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.
- You have to open the sheet containing the data. On the Developer Tab, choose the Macros option in the Code section.
- You need to make sure that your macro is selected and click the Run button.
- Finally, you will see the result as shown in the below image.
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) in Excel Office 365. Share your feedback in the below comment section. Thanks for visiting Geek Excel. Keep Learning!
Top comments (0)