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:
- Press
Alt + F11
in Excel. - Click on
Insert > Module
. - 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:
- Press
Alt + F8
in Excel. - Select
CommandButton1_click
. - 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