Loading
Excel.Tips.Net ExcelTips (Menu Interface)

Saving All Open Workbooks

Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Saving All Open Workbooks.

If you work with lots of workbooks open at the same time, you know that it can be a pain to go through and save each of the open workbooks, in turn. Wouldn't it be great to have a single command that allowed you to save all the open workbooks, without the need to do it manually?

Unfortunately, there isn't such a command. The closest solution is to hold down the Shift key as you click the File menu, then choose Close All. In the process of closing, Excel will ask if you want each workbook saved.

The big drawback to this is that Excel closes and you need to again start Excel and open all your workbooks. If you want a true Save All command, you need to create it using a macro. The following is a good example of one you could use:

Sub SaveAll()
    Dim Wkb As Workbook
    For Each Wkb In Workbooks
        If Not Wkb.ReadOnly And Windows(Wkb.Name).Visible Then
            Wkb.Save
        End If
    Next
End Sub

Save the macro in your Personal workbook, assign it to a toolbar button or a shortcut key, and you can call it up as often as you like. It saves all the workbooks that are open, except those that are read-only or hidden.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3307) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Saving All Open Workbooks.

Related Tips:

Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!

 

Leave your own comment:

*Name:
Email:
  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*Text:
*What is 5+3 (To prevent automated submissions and spam.)
 
 
           Commenting Terms

Comments for this tip:

Vicki    02 Jun 2016, 01:42
Many thanks, much needed.
Vanamali    19 Apr 2016, 09:58
Try to Save Workspace from View tab. This would save all the currently open worksheets and also let you save all the unsaved workspace, without closing any of the worksheets.
One more advantage is, even if you close all the tabs, you could open all the above worksheets by opening the workspace file .xlsw
Stephen Golden    31 Mar 2016, 14:32
Thanks for the tips for newer versions of Excel! I just upgraded to 2016 from 2003, and I'm amazed at how many useful features are either hidden or gone completely. I implemented the SaveAll macro, and I must have done something squirrelly because, even though it saves all the open workbooks, it leaves PERSONAL.XLSB open. Do you know what I did wrong and how I can fix it?
Rick Bajackson    28 Dec 2015, 16:10
Mr. Wyatt,

Thanks a lot for your tip on how to save all the open Excel spreadsheets with one command. I've been a user of Excel and prior to that Multiplan for a long time but missed the "upgrade" where MS decided that having one command to save all the open spreadsheets was a "waste of space".

Once again, thanks for the effort and then publishing your macro.
Ivan    03 Dec 2011, 15:42
Hi,

Very useful macro. I immediately transferred it into my growing library that I have maintained for many years. I had other workbook-related macros, but not an explicit SaveAll. The SaveAll is very elegant.

My library started with a macro from 'Source Wrox, John Green: Excel 2000 VBA, Chapter 12, Page 207' as an example. I still use the book although I am on Excel 2010.

You may be interested that after a 32 year career with IBM in system development and retiring 23 years ago, I switched to Apple, and am running Windows 7 under Apple's OS X Lion, on a 17" MacBook Pro.

A non-related question: More than a decade ago I changed over to the R1C1 notation in Excel, and would never consider going back to the A1 notation. How do you feel about R1C1? I don't know whether anybody is using it. A thread on this subject would be interesting.

Ivan R.
 
 

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us

 

Advertise with Us

Our Privacy Policy

Our Sites

Tips.Net

Beauty and Style

Cars

Cleaning

Cooking

DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2016)

Gardening

Health

Home Improvement

Money and Finances

Organizing

Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2016)

Our Products

Helpful E-books

Newsletter Archives

 

Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2016 Sharon Parq Associates, Inc.