How to Extract Data from Excel Using VBA

Manually sifting through massive Excel spreadsheets to find and copy specific pieces of information is a time-consuming and error-prone task. If this sounds like a regular part of your week, using Visual Basic for Applications (VBA) can fundamentally change your workflow. This article will walk you through how to use VBA to automate data extraction from Excel, turning hours of manual work into a single click.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

What is VBA and Why Use It?

Visual Basic for Applications (VBA) is the programming language built into Microsoft Office applications, including Excel. Think of it as a set of instructions you can give Excel to perform tasks on your behalf. For data extraction, it’s an incredibly powerful tool.

So, why bother learning a bit of code when you can just copy and paste? Here are a few key reasons:

• Save Time: The most obvious benefit. A task that takes you 30 minutes manually can be completed by a VBA script in seconds. This is especially true for repetitive tasks you perform daily, weekly, or monthly.
• Reduce Errors: Humans make mistakes. We might accidentally miss a row, copy the wrong data, or paste it in the incorrect place. A well-written script performs the exact same task perfectly every single time, ensuring consistency and accuracy.
• Handle Large Datasets: Trying to filter and copy data from a sheet with 50,000 rows can make Excel slow and unresponsive. VBA processes the data in the background, handling large volumes of information much more efficiently.
• Create Custom Logic: Your extraction needs may be more complex than a simple filter. Perhaps you need to extract rows where sales are over $500 and the region is "North," but only if the purchase date is in the last quarter. VBA allows you to build this kind of complex, custom logic that simple filters can't handle.

A Relatable Example

Imagine you have a master spreadsheet with sales data from across the country. Every Monday, your boss asks for a report of all sales from Texas. Your process is probably: open the master file, filter the "State" column for "Texas," select all the visible data, copy it, create a new worksheet, and paste the data. Next week, you do it all over again. With VBA, you could create a button that says "Extract Texas Sales" and a single click on that button would do all of those steps for you instantly.

Getting Started: Your VBA Environment

Before you can start writing code, you need to know where to put it. This involves enabling the Developer tab and opening the VBA editor.

1. Enable the Developer Tab

By default, Excel hides the Developer tab. To enable it:

1. Go to File > Options.
2. Click on Customize Ribbon in the left-hand pane.
3. In the right-hand box, under "Main Tabs," check the box next to Developer.
4. Click OK.

You should now see a "Developer" tab appear in your Excel ribbon.

2. Open the VBA Editor

The Visual Basic Editor (VBE) is where you’ll write your code. You can open it in two ways:

• Click the Visual Basic button on the far left of the Developer tab.
• Use the keyboard shortcut Alt + F11.

The editor will open in a new window. It might look intimidating, but you only need to know a few key areas for now: the Project Explorer (usually on the left), and the main code window (the big blank area on the right).

3. Insert a Module

Your VBA code lives inside a "module." To insert one, right-click anywhere in the Project Explorer, hover over Insert, and select Module. A new "Module1" will appear under a "Modules" folder, and the main code window is now ready for your first script.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Example 1: A Simple Extraction Script

Let's start with a basic task: copying a specific worksheet into a completely new Excel workbook. This is a common requirement when you need to send a single report to a colleague without sending the entire file.

Paste the following code into the module you just created:

Sub CopySheetToNewWorkbook()
    
    ' This line tells Excel we will use a variable called 'wsToCopy' to represent a worksheet.
    Dim wsToCopy As Worksheet
    
    ' Now we assign our active workbook's "SalesData" sheet to that variable.
    ' Make sure you have a sheet named "SalesData" or change the name to match your sheet.
    Set wsToCopy = ThisWorkbook.Sheets("SalesData")
    
    ' This is the action step. The .Copy method, when used on a sheet without a destination,
    ' automatically creates a new workbook and pastes the sheet there.
    wsToCopy.Copy
    
End Sub

How to Run Your Code

To run this macro:

• Click anywhere inside the code and press F5 from the VBA editor or:

1. Go back to your Excel workbook.
2. Click the Developer tab.
3. Click Macros. You'll see "CopySheetToNewWorkbook" in the list.
4. Select it and click Run.

Instantly, a new Excel workbook appears containing a perfect copy of your "SalesData" sheet.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Example 2: Extracting Rows Based on a Condition

Now for a more practical scenario. Let's say you have a master sheet named "AllSales" and you want to extract all rows where the value in the "Region" column is "West" and paste them into an existing sheet named "WestSales".

Here’s the step-by-step logic:

1. Identify the source and destination sheets.
2. Find the last row containing data on the source sheet to define our search range.
3. Loop through each row one by one.
4. In each row, check if the value in the "Region" column is "West."
5. If it is, copy that entire row to the next available empty row on the destination sheet.

Here is the code to accomplish this:

Sub ExtractWestRegionSales()

    ' --- Variable Declarations ---
    Dim sourceSheet As Worksheet
    Dim targetSheet As Worksheet
    Dim lastRow As Long
    Dim targetRow As Long
    Dim i As Long

    ' --- Setup ---
    Application.ScreenUpdating = False ' Makes the macro run faster and prevents screen flickering
    
    ' Assign our sheets to variables. Make sure your sheet names match.
    Set sourceSheet = ThisWorkbook.Sheets("AllSales")
    Set targetSheet = ThisWorkbook.Sheets("WestSales")
    
    ' --- Action ---
    ' 1. Clear any old data from the target sheet and copy headers.
    targetSheet.Cells.ClearContents
    sourceSheet.Rows(1).Copy Destination:=targetSheet.Rows(1)
    
    ' 2. Find the last row with data in the source sheet (checking column A).
    lastRow = sourceSheet.Cells(sourceSheet.Rows.Count, "A").End(xlUp).Row
    
    ' 3. Set the starting row for pasting data on the target sheet (row 2).
    targetRow = 2
    
    ' 4. Start the loop through each row of the source sheet.
    For i = 2 To lastRow 'Start at row 2 to skip headers
    
        ' 5. The core condition: Check if the value in Column C (the "Region" column) is "West".
        ' Adjust "C" if your "Region" column is different.
        If sourceSheet.Cells(i, "C").Value = "West" Then
        
            ' 6. If the condition is met, copy the entire row to the target sheet.
            sourceSheet.Rows(i).Copy Destination:=targetSheet.Rows(targetRow)
            
            ' 7. Increment the target row counter so the next paste is on the next line.
            targetRow = targetRow + 1
            
        End If
        
    Next i ' Move to the next row in the source sheet.
    
    ' --- Cleanup ---
    Application.ScreenUpdating = True ' Turn screen updating back on
    
    MsgBox "Extraction complete!" ' Let the user know it's done.

End Sub

Before running, make sure you have two sheets named "AllSales" (with data) and "WestSales" (can be empty). Running this code will instantly pull all relevant records into the "WestSales" sheet.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Example 3: Extracting Specific Columns

Sometimes you don't need the entire row, just a few specific columns. Let's modify the previous script to pull only the "Order Date", "Product Name", and "Sale Amount" for sales in the "West" region and place them into a summary sheet named "WestSummary".

Instead of copying the entire row, we will copy values cell by cell. Assume "Order Date" is in column B, "Product Name" is in column E, and "Sale Amount" is in column G on the "AllSales" sheet.

Paste the following code:

Sub ExtractSpecificColumnsForWestRegion()

    ' --- Variable Declarations (same as before) ---
    Dim sourceSheet As Worksheet
    Dim targetSheet As Worksheet
    Dim lastRow As Long
    Dim targetRow As Long
    Dim i As Long

    ' --- Setup ---
    Application.ScreenUpdating = False
    
    ' Assign our sheets to variables.
    Set sourceSheet = ThisWorkbook.Sheets("AllSales")
    Set targetSheet = ThisWorkbook.Sheets("WestSummary")
    
    ' --- Action ---
    ' 1. Clear target sheet and create new, specific headers.
    targetSheet.Cells.ClearContents
    targetSheet.Cells(1, 1).Value = "Date of Order"
    targetSheet.Cells(1, 2).Value = "Product Name"
    targetSheet.Cells(1, 3).Value = "Amount"
    
    ' 2. Find the last row.
    lastRow = sourceSheet.Cells(sourceSheet.Rows.Count, "A").End(xlUp).Row
    
    ' 3. Set the starting paste row.
    targetRow = 2
    
    ' 4. Loop through the rows.
    For i = 2 To lastRow
    
        ' 5. The same condition: Check if Region in Column C is "West".
        If sourceSheet.Cells(i, "C").Value = "West" Then
        
            ' 6. Instead of copying the whole row, copy individual cells' values.
            ' Col 1 in target gets value from Col B in source
            targetSheet.Cells(targetRow, 1).Value = sourceSheet.Cells(i, "B").Value
            
            ' Col 2 in target gets value from Col E in source
            targetSheet.Cells(targetRow, 2).Value = sourceSheet.Cells(i, "E").Value
            
            ' Col 3 in target gets value from Col G in source
            targetSheet.Cells(targetRow, 3).Value = sourceSheet.Cells(i, "G").Value
            
            ' 7. Increment the target row.
            targetRow = targetRow + 1
        End If
        
    Next i
    
    ' --- Cleanup ---
    Application.ScreenUpdating = True
    
    MsgBox "Column-specific extraction complete!"

End Sub

This approach gives you complete control over creating cleaner, more focused reports by choosing exactly which data points to extract.

Final Thoughts

Getting started with VBA can seem daunting, but it’s a powerful way to automate repetitive Excel tasks, especially for data extraction and reporting. By breaking down your goal into small, logical steps - looping through rows, checking conditions, and copying data - you can build scripts that save you countless hours and significantly improve your data accuracy.

While mastering VBA is perfect for complex Excel manipulations, the manual process of pulling data from different places - like Google Analytics, Shopify, and Salesforce - before it even gets to Excel is often the bigger time sink. If that sounds familiar, we built Graphed to solve this exact problem. We connect directly to your marketing and sales platforms, allowing you to build real-time, automated dashboards simply by describing what you want to see in plain English. There’s no need to combine CSVs or write code, just ask, "Show me my top-performing ad campaigns by revenue last month" and get an instant answer.