VBA & Macro Problems

How to Create an Excel Save and Close Button with VBA

Maira Mehtab Sep 22, 2026 869 views

Question details

The user wants to combine Excel VBA procedures to open a destination workbook, copy active records, and save and close both workbooks by clicking a form button.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Automating data transfer between multiple workbooks and triggering a sequence of saving and closing actions using a single macro button.
Observed behavior: The user needs to know the correct placement for event procedures (like Workbook_BeforeSave) and alternative methods to prevent Excel from slowing down or crashing during the automation.

Before you start

Ensure you have the Developer tab enabled in your Excel ribbon and that your file is saved as an Excel Macro-Enabled Workbook (.xlsm) to prevent any code loss.

Solution 1Recommended

Use a Dedicated Macro Assigned to a Form Button

Instead of triggering complex actions on every save, assign a dedicated macro to a custom button to safely open, copy, save, and close workbooks on demand.

Running heavy macros every time you save a file can drastically reduce performance. Using a form button gives you manual control over when the data transfer and close commands are executed.

Create the VBA Macro

Press Alt + F11 to open the VBA Editor. Go to Insert > Module to create a new standard module. Write your VBA script here to open the target workbook, perform the copy/paste actions, and use 'ActiveWorkbook.Close SaveChanges:=True' to save and close the files.

Insert a Form Button

Return to your Excel worksheet, navigate to the Developer tab, click on 'Insert', and select the 'Button (Form Control)' icon.

Draw and Assign

Click and drag anywhere on your worksheet to draw the button. An 'Assign Macro' dialog box will automatically appear. Select the macro you just created from the list and click 'OK'.

Best Practice: Triggering automation via a dedicated form button prevents the background processing delays commonly associated with automated save events.

Solution 2

Implement the Workbook_BeforeSave Event Code

If the automation must happen automatically upon saving, you can use the Workbook_BeforeSave event, provided the code is placed in the correct module.

Solution 3

Use Power Query to Import Data

A safer and more reliable alternative to VBA macros for transferring data is utilizing Power Query in the destination workbook.

WPS Spreadsheet Automation

How to Run Macros and Automate Tasks in WPS Spreadsheet

WPS Office offers comprehensive support for VBA macros (available in specific editions), allowing you to automate tasks like saving and closing workbooks seamlessly within a lightweight, highly compatible environment.

1. Enable Developer Tools: Open WPS Spreadsheet, navigate to the Options menu, and ensure the Developer tab is enabled so you can access all macro-related tools.
2. Launch the VBA Editor: Click on the Developer tab and select 'Visual Basic' or simply press Alt + F11 to open the VBA programming environment.
3. Insert Macro Code: Right-click in the Project Explorer, insert a new module, and paste your save and close macro script just as you would in standard VBA.
4. Assign to a Button: Use the Insert Button tool from the Developer tab to draw a button on your spreadsheet, then assign your newly created macro to it for easy one-click automation.

High compatibility with Microsoft Excel VBA scripts and .xlsm formats.Intuitive Developer tab for easy macro recording, editing, and button creation.Lightweight software architecture prevents system slowdowns during complex automation.Seamless migration of your existing Excel workbooks and scripts.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my Workbook_BeforeSave macro slow down Excel?

Placing complex procedures, such as opening a second workbook and copying large amounts of data, inside the Workbook_BeforeSave event forces Excel to run these tasks every single time you hit save. This constant background processing consumes memory and noticeably degrades performance.

Where exactly should I place the Workbook_BeforeSave VBA code?

Event codes that are triggered by workbook actions (like opening, closing, or saving) must be placed specifically in the 'ThisWorkbook' module within the VBA Editor. Placing them in a standard module (e.g., Module1) will cause the event triggers to fail.

Can I extract data from a closed Excel workbook without using VBA?

Yes. You can use Power Query to securely connect to and import data from a closed source workbook. Go to the Data tab, select 'Get Data', and choose 'From File' > 'From Excel Workbook' to set up a connection that bypasses the need for complex save-and-close macros.

How do I link a VBA macro to a clickable button in my worksheet?

First, ensure the Developer tab is visible. Go to the Developer tab, click 'Insert', and select the 'Button (Form Control)' icon. Click and drag to draw the button on your worksheet, and when the 'Assign Macro' window pops up, select your target macro from the list.

Excel & Data Problems