Data Backup Problems

How to Automatically Back Up an Excel Workbook to Another Drive

Algirdas Jasaitis Oct 10, 2026 873 views

Question details

The user wants to automatically save a copy of an Excel workbook to a secondary location, such as another network drive, for backup purposes.

How to Automatically Back Up an Excel Workbook to Another Drive

Product: Excel
Device & OS: not provided
Scenario: Setting up an automated backup process for an important spreadsheet across different drives or network locations.
Observed behavior: The user needs the file to automatically copy to a designated backup folder without manual intervention each time they save the primary file.

Before you start

Ensure you have read and write permissions for both the source drive and the target destination drive, and verify the exact folder path before setting up the automation.

Solution 1Recommended

Create a VBA Macro to Save Copies Automatically

Use a simple VBA script that triggers a backup save to your specified drive every time the workbook is saved.

Using a VBA macro is the most direct way to ensure your file is backed up exactly when you save it. By adding a BeforeSave event script, Excel will automatically place a copy in your target directory without requiring a separate task.

Open the VBA Editor

Open your Excel workbook and press Alt + F11 to launch the Visual Basic for Applications (VBA) Editor.

Access ThisWorkbook

In the Project Explorer pane on the left, double-click on 'ThisWorkbook'.

Insert the Backup Code

Select 'Workbook' from the left dropdown and 'BeforeSave' from the right dropdown. Insert the command: ActiveWorkbook.SaveCopyAs "F:\Data\Backup\" & ActiveWorkbook.Name.

Save as Macro-Enabled

Close the VBA editor and save your file as an Excel Macro-Enabled Workbook (.xlsm) to ensure the script runs on future saves.

Create a VBA Macro to Save Copies Automatically

Verification: Test the macro by saving your workbook normally. Check the backup drive to confirm the copy was successfully created.

Solution 2

Automate Backups Using Windows Task Scheduler

Set up a scheduled task in Windows to copy the file from your working drive to the backup drive at specific intervals.

Solution 3

Utilize OneDrive for Automatic Cloud Syncing

If you use Microsoft 365, rely on OneDrive's local synchronized folder to keep an automatic cloud backup.

Smart Data Protection

Easily Back Up Your Spreadsheets with WPS Office

WPS Office Spreadsheet offers built-in Auto Backup and AutoRecover features that automatically save backup versions of your work to a designated local folder, preventing data loss without the need for complex VBA macros.

1. Open WPS Spreadsheet: Launch WPS Office and open your spreadsheet file.
2. Access Options: Click the 'Menu' button in the top-left corner and select 'Options' from the dropdown list.
3. Navigate to General and Save: In the Options dialog box, click on the 'General and Save' tab on the left panel.
4. Enable Backup Settings: Under the 'Recover options' section, configure your backup intervals and enable the option to create a backup file.
5. Set Backup Directory: Specify your preferred backup directory or network drive path to ensure files are safely stored in your desired location.

Built-in local auto-backup functionality requires no codingFully compatible with Microsoft Excel (.xlsx) formatsLightweight application with an intuitive interfaceFree cloud storage available for automatic online syncing

microsoft office alternative - wps office

Frequently Asked Questions

Can I save an Excel file to two different drives at the same time without using macros?

Excel does not have a native built-in feature to save to two different local or network paths simultaneously without VBA. Using Windows Task Scheduler or third-party backup software is the best non-macro alternative.

Why isn't my VBA macro backing up the file automatically?

Ensure that you have saved your file as a Macro-Enabled Workbook (.xlsm) and that macros are enabled in your Excel Trust Center settings. If macros are disabled by your organization, the script will not execute.

Does OneDrive automatically back up files stored on a standard network drive?

No, OneDrive only synchronizes files stored within its designated local OneDrive folder. You must move the file from the network drive to the OneDrive folder for it to sync automatically.

How do I recover a previous version of my backed-up Excel file?

If you are using OneDrive, you can right-click the file and select 'Version history'. If using a macro with a timestamp in the file name, simply browse your backup drive and open the appropriately dated file.

Migration & Recovery