VBA Macro to Filter Excel Data and Create Separate Sheets Based on Unique Values

In this post, we explore a practical VBA macro designed to automate the process of filtering and copying data in Excel. If you often find yourself manually sorting and copying data based on specific criteria, this guide will show you how to streamline that process, saving you time and reducing the risk of errors.

Introduction

In today’s fast-paced business environment, efficiency is key. Manually filtering and copying data in Excel can be time-consuming and prone to errors.

Fortunately, Visual Basic for Applications (VBA) offers a powerful solution to automate these repetitive tasks. In this post, we’ll walk you through creating a VBA macro that filters data and copies it into respective sheets.

Prerequisites

Before we dive into the steps, make sure you have:

A basic understanding of Excel.
Familiarity with the VBA editor in Excel.
A sample Excel workbook with data

Suppose that we have a dataset of products where we have sales figures according to their region, as you can see in the image below: –

Now we want to create a new dataset from this data, showing product-wise sales in each region. To do this, we will apply filters, then copy the dataset and paste it into a new sheet to create a new dataset.

This process is straightforward with a small dataset, but when dealing with a large dataset, it can become challenging and time-consuming, potentially leading to errors.

However, with the help of VBA, we can accomplish this task easily. Just run the code, and all the work is done automatically.

Step-by-Step Guide – VBA Macro to Filter Excel Data

To begin, you need to access the VBA editor in Excel:

Press Alt + F11 on your keyboard. This will open the VBA editor.

Step 2: Insert a New Module

In the VBA editor:

Go to the Insert menu.
Click on Module. This will create a new module where you can write your code.

Step 3: Copy and Paste the VBA Code

Copy the following VBA code and paste it into the newly created module:

Sub AutoFilter_With_Create_NewSheets()
    Application.ScreenUpdating = False
    Dim x As Range
    Dim rng As Range
    Dim Last_R As Long
    Dim sht As String
    ‘specify sheet name in which the data is stored
    sht = “Data Set”
    ‘change filter column in the following code
    Last_R = Sheets(sht).Cells(Rows.Count, “A”).End(xlUp).Row
    Set rng = Sheets(sht).Range(“A1:H” & Last_R)
    Sheets(sht).Range(“A1:A” & Last_R).AdvancedFilter Action:=xlFilterCopy, copytorange:=Range(“AA1”), Unique:=True
    For Each x In Range((“AA2”), Cells(Rows.Count, “AA”).End(xlUp))
        With rng
            .AutoFilter
            .AutoFilter field:=1, Criteria1:=x.Value
            .SpecialCells(xlCellTypeVisible).Copy
            Sheets.Add(after:=Sheets(Sheets.Count)).Name = x.Value
            ActiveSheet.Paste
            Range(“A1”).CurrentRegion.Columns.AutoFit
        End With
    Next x
    ‘ turns off filter
    Sheets(sht).AutoFilterMode = False
    Application.ScreenUpdating = True
End Sub

VBA Macro to Filter Excel Data

Step 4: Understand the Code

Here’s a breakdown of what the code does:

Disables Screen Updating: Application.ScreenUpdating = False speeds up the macro by preventing screen refreshes during execution.

Defines Variables: Sets up variables to be used in the macro.

Specifies the Data Sheet: sht = “Data Set” specifies the sheet with your data. Change “Data Set” to your actual sheet name.

Determines the Last Row: Last_R – finds the last row with data in column A.

Sets the Range: Set rng – defines the range of data to be filtered.

Gets Unique Values: Uses AdvancedFilter – to copy unique values from column A to a new range starting at AA1.

Filters and Copies Data: For each unique value, the code filters the data and copies the visible cells to a new sheet named after the unique value.

Turns Off the Filter: Finally, the macro turns off the filter and re-enables screen updating.

Step 5: Run the Macro

To execute the macro:

Close the VBA editor.
Press Alt + F8 to open the Macro dialog box.
Select AutoFilter_With_Create_NewSheets.
Click Run.

Step 6: Verify the Results

Check your workbook. You should see new sheets created for each unique value in the specified column. Each sheet will contain the filtered data relevant to that unique value.

See that the product “Bikes” has a separate sheet as “Bikes” with all related data and same with other product also.

This code will filter unique data and create a new sheet and paste all the related data into it.

Conclusion

By using this VBA macro, you can save a significant amount of time and avoid manual errors when filtering and copying data into new sheets. VBA provides a powerful way to automate repetitive tasks in Excel, enhancing your productivity and efficiency.

How to Compare Two Excel Workbooks and Highlight Differences in Excel VBA

Calculate the Difference between Two Dates Dynamically

How to Extract Data Between Two Date Ranges in Excel VBA

Combine Data from Multiple Worksheets into a Single in Excel VBA

Excel VBA Macro: – Apply Filter and Copy, Paste Data Into Another Worksheet

3 Easy Methods to Transpose Data in Excel (Step-by-Step Guide)

How to Copy Data From a Closed Workbook in Excel VBA

How to Convert Excel Files to CSV Format File

How to Create Search Box in Excel (No VBA Code Required)

VBA Macro to Dynamically Copy and Paste from One Sheet to Another

How to Lock Scroll Area in Excel with VBA (Step By Step Guide)