How to split data from one sheet into multiple sheets using VBA?

Splitting data from one sheet into multiple sheets using VBA can be useful when you want to organize your data based on specific criteria, such as splitting the data by unique values in a particular column. Here is a step-by-step guide on how to do this:

Step 1: Set up your environment

Make sure you have your data in an Excel workbook and that you can access the VBA editor. Press `ALT` + `F11` in Excel to open the VBA editor.

Step 2: Insert a new module

In the VBA editor, go to `Insert` > `Module` to insert a new module where your VBA code will reside.

Step 3: Write the VBA code

Here is a sample VBA code that splits data based on unique values in a specific column:

Sub SplitDataIntoMultipleSheets()
    Dim ws As Worksheet
    Dim newWs As Worksheet
    Dim rng As Range
    Dim uniqueValues As Collection
    Dim cell As Range
    Dim newSheetName As String
    Dim dataSheet As Worksheet
    Dim header As Range
    Dim targetRange As Range
    Dim lastRow As Long
    Dim colIndex As Long
    
    ' Set the data sheet
    Set dataSheet = ThisWorkbook.Sheets("YourDataSheetName") ' Change "YourDataSheetName" to the name of your sheet
    
    ' Define the column index for unique values (e.g., 1 for column A, 2 for column B, etc.)
    colIndex = 1 ' Change this to the column number you want to use for splitting
    
    ' Define the range to consider (including the header)
    lastRow = dataSheet.Cells(dataSheet.Rows.Count, colIndex).End(xlUp).Row
    Set rng = dataSheet.Range(dataSheet.Cells(2, colIndex), dataSheet.Cells(lastRow, colIndex)) ' Start from row 2 to skip header

    ' Initialize the unique values collection
    Set uniqueValues = New Collection

    ' Populate the collection with unique values
    On Error Resume Next
    For Each cell In rng
        uniqueValues.Add cell.Value, CStr(cell.Value)
    Next cell
    On Error GoTo 0

    ' Loop through each unique value
    For Each item In uniqueValues
        newSheetName = item  ' Name the new sheet with the unique value
        ' Check for invalid sheet name characters and length
        newSheetName = Replace(newSheetName, ":", "")
        newSheetName = Replace(newSheetName, ""

facebooklinkedin