Macro to Save All Open Worksheets in Excel

 Last reviewed: September 2, 1997
Article ID: Q76442

SUMMARY

The following explains a macro that adds a Save All command to the File menu in Microsoft Excel. When selected, all files that have been saved previously will be saved automatically. If files are open that have not been saved previously, a dialog box will appear, prompting the user for a filename.

MORE INFORMATION

If you are using Excel version 3.0, you can use the add-in macro AUTOSAVE.XLA to save all open documents automatically as you work. This add-in macro is found in the EXCEL\LIBRARY subdirectory and is documented in the "Microsoft Excel User's Guide."

Enter the following information into a macro sheet: 

   A1: sav_menu
   A2: =ADD.COMMAND(1,"file",A10:C10,7)
   A3: =ADD.COMMAND(2,"file",A10:C10,7)
   A4: =ADD.COMMAND(4,"file",A10:C10,7)
   A5: =ADD.COMMAND(5,"file",A10:C10,7)
   A6: =ADD.COMMAND(6,"file",A10:C10,7)
   A7: =RETURN()
   A8:
   A9:
   A10: Sa&ve All          B10: Save   C10: Save all open documents
   A11:
   A12: Save
   A13: =ECHO(FALSE)
   A14: =FOR("i",1,COLUMNS(WINDOWS()),1)
   A15: =ACTIVATE(INDEX(WINDOWS(),1,i))
   A16: =GET.DOCUMENT(2)
   A17: =IF(ISERROR(B16),SAVE.AS?(),SAVE())
   A18: =NEXT()
   A19: =MESSAGE(TRUE,"All files have been saved!")
   A20: =ACTIVATE.PREV()
   A21: =WAIT(NOW()+0.00003)
   A22: =MESSAGE(FALSE,"")
   A23: =RETURN()
After entering the above information, define the two macros shown above. To do this:

  1. Select cell A1.

  2. From the Formula menu, choose Define Name. Select the Command option and choose the OK button.

  3. Select cell A12 and repeat step 2 above.

If you would like the Save All command to appear in the File menu every time you open this particular macro sheet, you must define an Auto_Open macro. You can do this by following steps 1 and 2 above, except that in step 2 you must type "Auto_Open" (without the quotation marks) in the Name text box before choosing the OK button.

For information about how to do this with a Visual Basic, Applications Edition, macro, please see the following article(s) in the Microsoft Knowledge Base: 

   ARTICLE-ID: Q131333
   TITLE     : "Visual Basic Macro to Save All Open Workbooks in MS Excel"

REFERENCES

"Microsoft Excel Functions and Macros," version 2.1

"Microsoft Excel User's Guide," version 3.0, pages 682-683

Additional query words: 2.10 2.10C 2.10D 3.00 2.20 2.21
Keywords : PgmHowTo PgmOthr

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: September 2, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.