Скачать или смотреть How to Get the Cell Value That Triggered a Macro in Excel VBA

Learn how to efficiently capture the cell value that triggers your macro in Excel VBA and copy the respective row to another workbook.
---
This video is based on the question https://stackoverflow.com/q/63618985/ asked by the user 'Bobholm' ( https://stackoverflow.com/u/14177261/ ) and on the answer https://stackoverflow.com/a/63619199/ provided by the user 'Tim Williams' ( https://stackoverflow.com/u/478884/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Get the cell value (eg. A6) of the cell which triggered the macro

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Get the Cell Value That Triggered a Macro in Excel VBA

In the world of Excel VBA, automating tasks can save an enormous amount of time and effort. One common scenario involves reacting to changes made in the worksheet. For instance, you might want to track changes in a specific range and copy the entire row to another workbook. This guide will address how to achieve that by providing you with a clear solution tailored to your needs.

Understanding the Problem

You might have a VBA macro that triggers whenever a change occurs in a designated range of cells, such as A2 to A50. You may want to know which specific row caused the macro to run, so you can copy that entire row to another workbook. Let’s break down the question:

Goal: Capture the cell value from the modified cell (e.g., A20) that triggered the macro.

Action Required: Copy the entire row of that cell to a different workbook whenever a change occurs in that range.

The Solution: Writing the VBA Code

To handle such modifications in your Excel sheet, you will use the Worksheet_Change event. Below is the updated code that can help you achieve your goal.

Updated Code

[[See Video to Reveal this Text or Code Snippet]]

Step-by-Step Breakdown

Declaration of Variables:

rng: This variable will hold the range of cells that intersect with the target of the change event.

c: This will be used to loop through individual cells in rng.

cDest: This is where we will find the next empty row in the destination workbook.

Identify the Intersected Range:

The Application.Intersect method checks if the modified cells (Target) are within your defined range (A2:A50).

Check for Non-Empty Intersection:

If the rng is not empty, it indicates that changes have occurred within the specified range.

Set the Destination:

The code finds the next empty row in the "Data" sheet of the "data.xlsx" workbook.

Copy the Entire Row:

Using a loop, each row corresponding to the changed cell is copied to the selected cDest location.

Best Practices

Avoiding Calls: If you're just starting in VBA, it's advisable to avoid the use of Call. While it’s still functional, it's considered somewhat outdated and unnecessary for modern VBA coding.

Conclusion

The above code will effectively allow you to capture any modifications in the specified range and copy the respective entire row to another workbook. This capability is particularly handy for tasks such as data analysis and record-keeping, where changes need to be tracked efficiently.

Now you can enhance your Excel automation skills with this nifty trick! If you have any questions or need further clarification, feel free to reach out. Happy coding!