How to Copy Row Data to Another Workbook Using Excel VBA
Question details
The user needs to create an Excel VBA macro that automatically copies specific columns (A and E) from a selected row and pastes them into another workbook's columns (C and D), starting at row 51 and using the next available row.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Automating data transfer between two different workbooks based on user interaction with a specific row.
- Observed behavior
- The user requires a functional VBA script that identifies the selected row, prevents standard cell editing upon click, and successfully appends the targeted column values to the destination workbook.
Ensure that both the source and destination workbooks are saved in a macro-enabled format (.xlsm), macros are enabled in your Trust Center settings, and both files are open during execution.
Use the Worksheet_BeforeDoubleClick Event to Transfer Data
This method allows you to instantly copy data from specific columns to another open workbook simply by double-clicking a cell in the desired row.
Using a Worksheet event instead of a standard macro button provides a seamless workflow. By incorporating 'Cancel = True' in the script, you prevent Excel from entering edit mode when double-clicking the cell, ensuring the macro runs smoothly.
In your source workbook, press ALT + F11 to launch the Visual Basic for Applications (VBA) Editor.
In the Project Explorer pane on the left, double-click the specific Sheet module (e.g., Sheet1) where you want the double-click action to be active.
Create a 'Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)' procedure. Use 'Target.Row' to identify the selected row, and write the script to copy the values from columns A and E.
In your code, specify the destination workbook name and worksheet name. Use the 'End(xlUp)' method to find the next available row in columns C and D, ensuring it starts at or below row 51.
Add 'Cancel = True' before the end of your Sub to disable the default cell editing behavior upon double-click.

Automate Your Workflows Using VBA in WPS Spreadsheet
WPS Office provides robust support for VBA and Macros, allowing you to run, edit, and create automation scripts like the double-click event just as you would in Microsoft Excel.
- 1. Install WPS Office: Download and install the free version of WPS Office on your device.
- 2. Open Your Macro File: Launch WPS Spreadsheet and open your existing .xlsm file containing the data.
- 3. Access the Developer Tools: Navigate to the 'Developer' tab on the top ribbon and click 'Visual Basic' to open the VBA editor.
- 4. Run Your Script: Paste your Worksheet_BeforeDoubleClick script into the desired sheet module, save, and double-click a row to test the automation.

Frequently Asked Questions
Why do I get a 'Subscript out of range' error when running this macro?
This error usually occurs if the destination workbook is not currently open in the background, or if the workbook and worksheet names specified in the VBA code do not exactly match the actual open file names.
How do I find the next available empty row in a target workbook using VBA?
You can find the next empty row dynamically by using the End(xlUp) property. A common syntax is: NextRow = Workbooks("Destination.xlsx").Sheets("Sheet1").Cells(Rows.Count, "C").End(xlUp).Row + 1.
Can I assign this VBA action to a clickable button instead of a double-click event?
Yes. You can insert a Form Control Button from the Developer tab, place it on your sheet, and assign a standard Sub macro to it. However, the macro will need to reference 'ActiveCell.Row' instead of 'Target.Row' to identify which row's data to copy.
Does WPS Office support Excel VBA macros?
Yes, WPS Office provides excellent VBA support. By using the appropriate version with the VBA module installed, you can easily run, edit, and create macros that are fully compatible with Excel's .xlsm files.




