VBA & Macro Problems

How to Copy Row Data to Another Workbook Using Excel VBA

Rana GarciaRana Garcia Nov 4, 2026 871 views

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.

How to Copy Row Data to Another Workbook Using Excel VBA
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.
Before you start

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.

Solution 1Recommended

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.

1
Open the VBA Editor

In your source workbook, press ALT + F11 to launch the Visual Basic for Applications (VBA) Editor.

2
Access the Worksheet Module

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.

3
Insert the VBA Code

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.

4
Define the Destination

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.

5
Set Cancel to True

Add 'Cancel = True' before the end of your Sub to disable the default cell editing behavior upon double-click.

Use the Worksheet_BeforeDoubleClick Event to Transfer Data
Destination File Requirements: Make sure the destination workbook is currently open in Excel when you trigger the double-click event; otherwise, the VBA script will return a 'Subscript out of range' error.
Advanced VBA Support in WPS

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. 1. Install WPS Office: Download and install the free version of WPS Office on your device.
  2. 2. Open Your Macro File: Launch WPS Spreadsheet and open your existing .xlsm file containing the data.
  3. 3. Access the Developer Tools: Navigate to the 'Developer' tab on the top ribbon and click 'Visual Basic' to open the VBA editor.
  4. 4. Run Your Script: Paste your Worksheet_BeforeDoubleClick script into the desired sheet module, save, and double-click a row to test the automation.
Fully compatible with Microsoft Excel (.xlsm) macro-enabled formats.Built-in VBA editor to write and debug scripts seamlessly.Lightweight software architecture that loads large datasets faster.Free access to fundamental spreadsheet tools and professional functions.
microsoft office alternative - wps office

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.