VBA & Macro Problems

How to Back Up an Excel Workbook to Another Network Location using VBA

Guest Writer Oct 10, 2026 872 views

Question details

The user needs to back up an Excel workbook stored on one network drive and save it to a different network location using VBA.

How to Back Up an Excel Workbook to Another Network Location using VBA

Product: Excel
Device & OS: not provided
Scenario: Creating an automated script to save a backup copy of a workbook across different network folders while handling potential network availability errors.
Observed behavior: Excel's built-in 'Always Create a Backup' feature does not allow users to redirect the backup file to a different folder natively.

Before you start

Ensure you have the Developer tab enabled in your spreadsheet program and verify that your computer has write access to the destination network drive.

Solution 1Recommended

Use SaveCopyAs to Back Up the Active Workbook

This is the best method to create a backup of your currently open workbook without changing the active file you are working on.

The ActiveWorkbook.SaveCopyAs method saves a copy of the current workbook to a specified location while leaving the original file open and unchanged in the application.

Open the VBA Editor

Press Alt + F11 to launch the Visual Basic for Applications (VBA) editor in your spreadsheet program.

Insert a New Module

Right-click on your workbook name in the Project Explorer panel, select Insert, and then click Module.

Enter the SaveCopyAs Macro

Type the code: ActiveWorkbook.SaveCopyAs "Y:\Headcount\Headcount.xlsm". Ensure you replace the path with your actual destination network drive.

Add Error Handling

Wrap the code with 'On Error GoTo ErrorHandler' to catch situations where the network path is disconnected or unavailable.

Use SaveCopyAs to Back Up the Active Workbook

Preserves Active File: Unlike SaveAs, SaveCopyAs does not switch your current session to the newly saved file.

Solution 2

Use FileCopy to Transfer an Existing File

Use this method if you need to copy a closed workbook or a different file entirely from one network location to another.

Advanced Spreadsheet Automation

Automate Your Workbook Backups Seamlessly in WPS Office

WPS Office provides robust and highly compatible support for VBA macros. You can run all standard workbook backup commands like SaveCopyAs and FileCopy natively to protect your valuable data.

1. Open Your Workbook: Launch WPS Spreadsheets and open the workbook you want to back up.
2. Access the Developer Tools: Navigate to the Developer tab on the ribbon and click on the VBA Editor.
3. Run Your Backup Macro: Paste your SaveCopyAs script into a new module and press F5 to execute the backup to your network drive.

Full support for Microsoft Excel VBA scripts and macrosSeamlessly save and load .xls and .xlsm formatsLightweight architecture for faster file handling over networksFree to use with a familiar, user-friendly interface

microsoft office alternative - wps office

Frequently Asked Questions

Can I use Excel's 'Always Create a Backup' option to save to a different folder?

No, the built-in 'Always Create a Backup' feature only saves the backup file (.xlk) in the exact same directory as the original file. To redirect the backup to another network drive, you must use a VBA macro.

What is the difference between SaveAs and SaveCopyAs in VBA?

SaveAs changes your current active workbook to the new file you just saved, closing the original. SaveCopyAs leaves your original workbook open and active while silently saving a duplicate to the location you specify.

Why does my backup macro fail when saving to a network drive?

Macro failures over networks are typically caused by mapped drives being disconnected, lack of write permissions, or network latency. It is highly recommended to add basic error handling in your VBA code to prompt users when the drive is unreachable.

Excel & Data Problems