How to Automatically Organize Data into Separate Sheets Based on Criteria in Excel Using VBA

Working with large datasets in Excel can be challenging especially when you have to manually organize data into separate sheets based on specific criteria.

This is where VBA comes in. In this guide, we’ll cover how to automatically organize data into separate sheets based on criteria in Excel using VBA.

This method will save you a lot of time by automating the process and helping you manage your data more efficiently

Setting Up the VBA Macro

Before starting, ensure that your Excel workbook has a sheet with the data you want to organize. The data should have headers, with at least one column containing the categories you want to use for splitting (for instance, cities).

To open the VBA editor:

1. Press Alt + F11 in Excel.
2. Click on Insert > Module.
3. Paste the VBA code into the module.

Breaking Down the Code (Line-by-Line)

Let’s break down the VBA code to explain what each section does:

Option Explicit The compiler is forced to check the declaration of all variables before the code is executed.

Option Explicit

Private Sub CommandButton1_click() A private subroutine is defined, triggered when a button named CommandButton1 is clicked.

Private Sub CommandButton1_click()

Constants and Variable Declarations then Constants and variables are declared.

Const col = "D"
Const header_row = 1
Const starting_row = 2

Dim source_sheet As Worksheet
Dim destination_sheet As Worksheet
Dim source_row As Long
Dim last_row As Long
Dim destination_row As Long
Dim city As String

Then The source sheet is set to the currently active sheet.

Set source_sheet = ActiveSheet

The last row with data in the specified column is found.

last_row = source_sheet.Cells(source_sheet.Rows.Count, col).End(xlUp).Row

Each row in the source sheet is iterated through, starting from the starting_row and ending at the last_row.

For source_row = starting_row To last_row

Then The city value is obtained from the current row.

vbVerifyOpen In EditorEditCopy code<code>1city = source_sheet.Cells(source_row, col).Value

Then It is checked if a sheet for the current city already exists. If not, the destination_sheet is set to Nothing, and a new sheet is created.

Set destination_sheet = Nothing
On Error Resume Next
Set destination_sheet = Worksheets(city)

On Error GoTo 0

If destination_sheet Is Nothing Then
    Set destination_sheet = Worksheets.Add(after:=Worksheets(Worksheets.Count))
    destination_sheet.Name = city
    source_sheet.Rows(header_row).Copy Destination:=destination_sheet.Rows(header_row)
End If

Then The destination row in the destination sheet is found.

destination_row = destination_sheet.Cells(destination_sheet.Rows.Count, col).End(xlUp).Row + 

Then Finally Data is copied from the source sheet to the destination sheet.

source_sheet.Rows(source_row).Copy Destination:=destination_sheet.Rows(destination_row)
Next source_row

Full Code Example

Here’s the full VBA code that you can copy and paste into the VBA editor:

Option Explicit
Private Sub CommandButton1_click()
    Const col = "D"
    Const header_row = 1
    Const starting_row = 2
    
    Dim source_sheet As Worksheet
    Dim destination_sheet As Worksheet
    Dim source_row As Long
    Dim last_row As Long
    Dim destination_row As Long
    Dim city As String
    
    Set source_sheet = ActiveSheet
    last_row = source_sheet.Cells(source_sheet.Rows.Count, col).End(xlUp).Row
    
    For source_row = starting_row To last_row
        city = source_sheet.Cells(source_row, col).Value
        
        Set destination_sheet = Nothing
        On Error Resume Next
        Set destination_sheet = Worksheets(city)
        On Error GoTo 0
        
        If destination_sheet Is Nothing Then
            Set destination_sheet = Worksheets.Add(after:=Worksheets(Worksheets.Count))
            destination_sheet.Name = city
            source_sheet.Rows(header_row).Copy Destination:=destination_sheet.Rows(header_row)
        End If
        
        destination_row = destination_sheet.Cells(destination_sheet.Rows.Count, col).End(xlUp).Row + 1
        source_sheet.Rows(source_row).Copy Destination:=destination_sheet.Rows(destination_row)
    Next source_row
End Sub

Running the Code

To run the macro:

1. Press Alt + F8 in Excel.
2. Select CommandButton1_click.
3. Click Run.

The macro will go through each row in your data and create a new sheet for each unique city in column D. Data from each city will be copied into its respective sheet.

Here is a flow chat of the code

Result

Before

After

So After running the macro, you will find new sheets named after the cities (or the categories in column D). Each sheet will contain all the rows corresponding to that city from the original data.

Conclusion

This VBA code allows you to automatically split data into different sheets based on a specific column, such as a list of cities. By organizing your data in this way, you save time and ensure that related information is stored together in separate sheets.

if you like this blog post on How to Automatically Organize Data into Separate Sheets Based on Criteria in Excel Using VBA then you would love our other blog post on How to Consolidate Data from Multiple Excel Files with VBA in One Click