VBA & Macro Problems

How to Find a Value and Copy Cells Below It Using Excel VBA

Anum Mushtaq Oct 21, 2026 868 views

Question details

The user needs a method to search an entire Excel worksheet for a specific text label (such as 'Lease Expiry Date') and copy the data situated directly beneath it to a fixed location on another sheet.

How to Find a Value and Copy Cells Below It Using Excel VBA

Product: Excel
Device & OS: not provided
Scenario: Extracting specific dynamic data below a known column header or text label when the worksheet layout is unpredictable and prone to variation.
Observed behavior: The target values are located directly beneath a specific text label, requiring an automated script to locate the label and transfer the subsequent data range to a new destination worksheet.

Before you start

Before running the macro, ensure you have enabled the Developer tab in Excel and saved your workbook as a Macro-Enabled Workbook (.xlsm).

Solution 1Recommended

Use a VBA Macro to Loop and Find Values

Create a VBA script to loop through the worksheet's used range, find the target label, and copy the cells below it to a designated destination sheet.

By utilizing a VBA macro, you can automatically iterate through a defined number of rows and columns to find an exact text match. Once found, the script copies a defined range of cells beneath the match and pastes them into another worksheet, ensuring accurate data extraction regardless of layout changes.

Open the VBA Editor

Press 'Alt + F11' on your keyboard to launch the Microsoft Visual Basic for Applications (VBA) editor.

Insert a New Module

In the top menu, click on 'Insert' and select 'Module' to create a blank window for your code.

Input the VBA Code

Copy and paste the appropriate macro code into the module. Ensure you write a standard loop (e.g., 'For i = 1 To 1000' and 'For j = 1 To 100') containing an 'If' condition that checks for your specific label, such as 'If Cells(i, j).Value = "Lease Expiry Date" Then'.

Define Copy and Paste Ranges

Inside your 'If' condition, define the copy range starting from the row directly below the match (e.g., 'Range(Cells(i + 1, j), Cells(i + 1000, j)).Copy'). Then, specify your destination sheet and cell, such as 'Sheets("SheetName").Range("F6").PasteSpecial'.

Run the Macro

Close the VBA editor or press 'F5' while inside the module to execute the macro. The script will search the sheet and transfer the required values.

Adjusting the Search Range: In your macro, adjust the loop limits or use the 'UsedRange' property to ensure the macro covers all data in your specific worksheet without unnecessarily slowing down processing time.

Automate Data Extraction with WPS Office Spreadsheets

WPS Office fully supports VBA (Visual Basic for Applications) and Excel macros, allowing you to seamlessly run your scripts to find and copy dynamic data without rewriting your code.

1. Open your Workbook in WPS Office: Launch WPS Office Spreadsheets and open your Macro-Enabled Workbook (.xlsm).
2. Access Developer Tools: Navigate to the 'Developer' tab on the top ribbon and click on 'Macros' or 'Visual Basic'.
3. Execute the Search Macro: Select your previously written macro from the list and click 'Run' to instantly locate your label and copy the target cells.

100% compatible with Microsoft Excel .xlsx and .xlsm formatsBuilt-in VBA editor for creating, running, and editing macros seamlesslyLightweight application with fast data processing speedsCompletely free to download with a familiar, intuitive interface

Frequently Asked Questions

How do I search for a partial text match instead of an exact match in VBA?

You can use the 'InStr' function in VBA or the 'Range.Find' method with the parameter 'LookAt:=xlPart'. This allows the script to locate cells that contain your target text alongside other characters, rather than requiring an exact full-cell match.

Why is my VBA macro returning a 'Subscript out of range' error?

This error usually occurs when the destination worksheet name specified in the code (e.g., 'SheetName') does not perfectly match any sheet in your active workbook. Check for typos, spelling errors, or trailing spaces in the sheet tabs.

Can I copy the extracted values to a different workbook entirely?

Yes. You must explicitly reference the target workbook in your VBA code, such as 'Workbooks("TargetBook.xlsx").Sheets("Sheet1").Range("A1").PasteSpecial', and ensure that the target workbook is currently open in Excel.

How do I stop the macro after finding the very first match?

You can add an 'Exit Sub' or 'Exit For' statement immediately after the paste operation inside your 'If' block. This prevents the macro from continuing to loop through the rest of the worksheet once the first valid dataset is copied.

Excel & Data Problems