Excel VBA Split Excel Sheet into Multiple Sheets

To split an Excel sheet into multiple sheets using VBA (Visual Basic for Applications), we can use the following code as a starting point. This code assumes that we want to split the data in the active sheet based on a specific column value into separate sheets.

Visual Basic 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
        Application.Worksheets.Add after:=Application.Worksheets(Application.Worksheets.Count)
        Set xRow = xRow.Offset(SplitRow)
    Application.CutCopyMode = FALSE
    Application.ScreenUpdating = TRUE
End Sub


Software Engineer | Ethical Hacker & Cybersecurity...

Md Obydullah is a software engineer and full stack developer specialist at Laravel, Django, Vue.js, Node.js, Android, Linux Server, and Ethichal Hacking.