VBA & Macro Problems

Use VBA to Copy a Value When a Cell Condition is TRUE in Excel

Steve K Oct 20, 2026 868 views

Question details

The user needs a VBA script to automate copying values from column AW to column AN for rows 7 through 55 whenever the corresponding cell in column AV (linked to a checkbox) becomes TRUE.

How to Automatically Copy a Value Using VBA When a Cell is TRUE

Product: Spreadsheets
Device & OS: not provided
Scenario: Automating data entry updates using checkboxes and a Worksheet_Change event script, replacing manual button-triggered macros.
Observed behavior: The user is struggling to get the macro to trigger automatically when the cell value changes to TRUE within the Worksheet_Change event context.

Before you start

Ensure that your spreadsheet allows macros (saved as an .xlsm file) and that Developer tools are enabled in your ribbon. Verify that the cells in column AV are correctly formatted to output boolean TRUE/FALSE values (e.g., linked to Form Control checkboxes).

Solution 1Recommended

Implement a Worksheet_Change Event Macro

Use this specific VBA script in the target worksheet module to automatically trigger the copy action whenever a cell in the AV column changes.

This solution utilizes the Worksheet_Change event to continuously monitor rows 7 to 55 in column AV. It prevents recursive infinite loops by temporarily disabling application events and avoids unnecessary calculations by comparing the destination and source values before copying.

Open the VBA Editor

Press Alt + F11 on your keyboard to open the Visual Basic for Applications (VBA) Editor. In the Project Explorer on the left, double-click the specific Worksheet name where your data resides (do not use a standard Module).

Paste the Event Code

Copy and paste the Private Sub Worksheet_Change(ByVal Target As Range) code block provided in the accepted answer into the blank code window.

Verify Range and Conditions

Ensure the code contains 'Intersect(Target, Me.Range("AV7:AV55"))' to restrict execution strictly to the target cells. The logic 'If CBool(cell.Value) = True Then' handles the checkbox toggle condition.

Save and Test

Save your workbook as a Macro-Enabled Workbook (.xlsm). Return to the spreadsheet and check one of the boxes linked to column AV to verify that the value from AW copies over to AN instantly.

Implement a Worksheet_Change Event Macro

Formula Update Workaround: If column AV changes due to a formula calculation rather than a direct edit or a linked checkbox toggle, the Worksheet_Change event will not trigger. You must use the Worksheet_Calculate event instead.

Advanced Spreadsheet Features

Run VBA Macros Seamlessly in WPS Spreadsheet

WPS Office provides robust native support for VBA and macros, allowing you to automate tasks like copying cells based on checkbox conditions exactly as you would in Microsoft Excel.

1. Install WPS Office: Download and install WPS Office on your device.
2. Open your Workbook: Open your macro-enabled spreadsheet (.xlsm) in WPS Spreadsheet.
3. Enable Developer Tools: Navigate to the 'Developer' tab and click 'Macros' or 'Visual Basic' to manage your event-driven code seamlessly.

Full compatibility with Microsoft Excel VBA scripts and .xlsm filesLightweight software with fast macro execution speedsBuilt-in Developer tools to easily edit, test, and debug VBA codeFree to use with an intuitive, familiar tabbed interface

microsoft office alternative - wps office

Frequently Asked Questions

Why isn't my Worksheet_Change macro triggering when the cell value changes?

Worksheet_Change only triggers upon direct user edits or when toggling linked Form Control checkboxes. If the cell value changes as the result of a formula recalculating, the event will not fire. In that case, use the Worksheet_Calculate event.

Can I place this VBA code in a standard module?

No, event-handling macros like Worksheet_Change must be placed within the specific Worksheet object module (e.g., Sheet1) in the VBA editor. They will not function if placed in standard modules (e.g., Module1).

How do I link a checkbox to a cell so it outputs TRUE or FALSE?

Insert a Form Control Checkbox from the Developer tab, right-click it, select 'Format Control', navigate to the 'Control' tab, and set the 'Cell link' to your desired cell (e.g., AV7). When the box is checked, the cell will automatically display TRUE.

Why does the macro disable events using Application.EnableEvents = False?

Because the macro modifies cell values within the worksheet (copying AW to AN), those changes could inadvertently trigger the Worksheet_Change event again, creating an infinite loop that crashes the application. Disabling events temporarily prevents this.

Excel & Data Problems