How to Use VBA to Open Another Sheet When a Cell Is Clicked in Spreadsheet
Question details
The user needs a VBA macro that triggers when cell A3 is selected, which will then activate Sheet2 and copy the clicked cell's value along with its adjacent cell's value (B3) into A1 and B1 of Sheet2.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Automating navigation and data transfer between worksheets based on a user's cell selection.
- Observed behavior
- The goal is to automatically switch to Sheet2 and paste values from A3 and B3 into Sheet2's A1 and B1 exactly when Sheet1's A3 is clicked.
Ensure that your workbook is saved as a Macro-Enabled Workbook (.xlsm) and that you have enabled macros in your spreadsheet trust center or security settings.
Use Worksheet_SelectionChange Event to Switch Sheets and Copy Values
Apply a VBA macro to the specific worksheet module to detect clicks on a target cell, copy data, and redirect the user automatically.
To execute code immediately when a user clicks a cell, you must use the Worksheet_SelectionChange event. This special event must be placed directly in the code module of the worksheet where the interaction happens, rather than in a standard module.
Press ALT + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor in your spreadsheet application.
In the Project Explorer panel on the left side, double-click on 'Sheet1' (or the exact name of the sheet where you want the click action to trigger). Do not insert a new standard module.
Copy and paste the following code into the blank code window: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim targetSheet As Worksheet If Not Intersect(Target, Me.Range("A3")) Is Nothing Then Set targetSheet = ThisWorkbook.Worksheets("Sheet2") targetSheet.Range("A1").Value = Target.Value targetSheet.Range("B1").Value = Target.Offset(0, 1).Value targetSheet.Activate End If End Sub
Close the VBA editor and return to your spreadsheet. Go to Sheet1 and click on cell A3. The spreadsheet should immediately jump to Sheet2, copying the contents of Sheet1!A3 to Sheet2!A1 and Sheet1!B3 to Sheet2!B1.
Automate Worksheets Seamlessly with WPS Spreadsheet
WPS Spreadsheet fully supports VBA macros, allowing you to create event-driven actions like jumping between sheets and copying cell data instantly.
- 1. Open WPS Spreadsheet: Launch WPS Office and open your Macro-Enabled Workbook.
- 2. Enable the Developer Tools: Navigate to the Developer tab on the top ribbon. If you don't see it, enable it via the settings menu.
- 3. Insert Your VBA Code: Click 'Visual Basic' to access the VBA editor, select your target sheet in the Project Explorer, and paste your automation script.
Frequently Asked Questions
How do I change the trigger cell from A3 to another cell?
Modify the `Me.Range("A3")` portion in the `Intersect` function to your desired cell reference. For example, change it to `Me.Range("C5")` to trigger the macro when C5 is clicked.
Will this code overwrite existing data in Sheet2 A1 and B1?
Yes. Every time the target cell (A3) is clicked, the macro will execute and overwrite whatever data is currently in cells A1 and B1 on the destination sheet.
Why doesn't the macro run when I click the cell?
Ensure macros are enabled in your Trust Center or Security settings. Also, verify that the code was pasted into the specific Worksheet module (e.g., 'Sheet1') and not into a 'Module1' or 'ThisWorkbook' object.
How can I copy more adjacent columns?
You can add more lines before the `targetSheet.Activate` command using the `Offset` property. For example, `targetSheet.Range("C1").Value = Target.Offset(0, 2).Value` will copy the value from C3 (which is two columns to the right of A3).
