VBA & Macro Problems

How to Use Excel VBA Macros to Transfer Data Between Sheets in Shared Workbooks

WPS Content Manager Oct 1, 2026 869 views

Question details

The user needs an automated way to transfer discharged patient data from one sheet to another using a VBA macro, but the macro stops functioning when team members edit the shared workbook via OneDrive.

How to Use Excel VBA Macros to Transfer Data Between Sheets in Shared Workbooks

Product: Microsoft Excel
Device & OS: not provided
Scenario: Collaborating on a shared workbook to manage patient discharges and transferring specific row data to a separate destination table automatically.
Observed behavior: The VBA macro triggers successfully on the local desktop application but fails to run when other users open and edit the shared workbook online using Excel for the web.

Before you start

Ensure your workbook is saved as an Excel Macro-Enabled Workbook (.xlsm) and verify that you are opening it in the desktop version of Excel, as macros cannot run natively in a web browser.

Solution 1Recommended

Open the Shared Workbook in Desktop Excel

Since Excel for the web does not support VBA macros, team members must open the file in the desktop application to run the automated data transfer.

VBA macros are fundamentally incompatible with browser-based versions of Excel, even when the file is stored and shared via OneDrive. To allow team members to trigger the automated data transfer when they mark a patient as discharged, they must transition from the web interface to the desktop client.

Navigate to the shared file

Open your shared workbook in OneDrive or Excel for the web as you normally would.

Open in Desktop App

Click on the 'Editing' or 'Viewing' drop-down menu located in the top ribbon, and select 'Open in Desktop App' to launch the fully-featured Excel desktop client.

Enable macros

When Excel opens, look for the yellow security warning bar at the top of the worksheet and click 'Enable Content' to allow the VBA macro to run.

Trigger the transfer

Enter your discharge marker (e.g., 'X') in column O. The macro will now execute, moving the row data to your destination sheet.

Open the Shared Workbook in Desktop Excel

Alternative Automation: If your team strictly works in the browser and cannot use the desktop app, you will need to rewrite the automation using Office Scripts or Power Automate, which are supported by Excel for the web.

Solution 2

Implement the Worksheet_Change Event Macro

Add a background VBA script to detect the discharge marker in column O and automatically move the target data to another worksheet.

Free Microsoft Office alternative

Use WPS Office for Seamless VBA Macro Support

If limitations in Excel for the web are creating collaboration bottlenecks for your automated tasks, WPS Office offers a highly capable and lightweight desktop alternative. With excellent built-in VBA support, your automation scripts run smoothly without the need for expensive subscription fees.

Highly compatible with Microsoft Excel (.xlsx and .xlsm) macro-enabled formats.Built-in robust support for VBA macros to easily automate data transfer tasks.Lightweight installation with a familiar, easy-to-navigate user interface.Completely free alternative to expensive Microsoft Office subscriptions.

microsoft office alternative - wps office

Frequently Asked Questions

Why do my Excel VBA macros stop working when shared via OneDrive?

Excel for the web does not support VBA macros. When multiple users open and edit a shared workbook simultaneously in their web browsers via OneDrive, the background VBA scripts cannot execute. Users must click 'Open in Desktop App' to run any macro-enabled functions.

How do I save a workbook that contains VBA macros?

Workbooks containing macros must be saved as an Excel Macro-Enabled Workbook. Navigate to File > Save As, and choose '.xlsm' from the file format dropdown menu. If you save it as a standard .xlsx file, all VBA code will be permanently removed.

Are there alternatives to VBA for web-based Excel collaboration?

Yes. If your team relies exclusively on Excel for the web, you can recreate your automation using Office Scripts or Microsoft Power Automate. These modern, cloud-based tools are natively supported in the browser and can perform similar automated data transfer tasks.

How can I automatically clear the original row after transferring data without losing formatting?

Within your Worksheet_Change VBA event, after copying the range to the destination sheet, use the 'Range.ClearContents' method on the source row. This deletes the text values but preserves the cell background colors, borders, and fonts.

Excel & Data Problems