Macro to Save All Open Worksheets in Excel

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:

Select cell A1.

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

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 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:

Q131333 "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 :
Version :
Platform :
Issue type :