hirota

Excel: Macro to save all sheets with A1 cell selection & zoom 100%

Web Tools
Sub ResetView()
    Dim sheet As Object
    For Each sheet In ActiveWorkbook.Sheets
        sheet.Activate
        ActiveSheet.Range("A1").Select
        ActiveWindow.Zoom = 100
    Next sheet
    ActiveWorkbook.Sheets(1).Activate

    If ActiveWorkbook.Path = "" Then
        Application.Dialogs(xlDialogSaveAs).Show
    Else
        ActiveWorkbook.Save
    End If
End Sub

This macro performs five tasks.

  1. Set the selected cell in all sheets to A1.
  2. Set the zoom of all sheets to 100%.
  3. Scroll to the top.
  4. Activate the first sheet.
  5. Save the workbook.

It is useful when used as an add-in.

How to create macros and add-ins

If you save the macro as an add-in (.xlam), you can use it in all workbooks. It is easy and recommended to add it to the Quick Access Toolbar.

Create an add-in (.xlam)

  1. Open the editor from the “Developer > Visual Basic”. If the Developer tab is not displayed, go to “File > Options > Customize Ribbon” and check the Developer checkbox.
  2. Go to “Insert” and add “Module”.
  3. Paste the code into “Module1” (the name does not have to be Module1).
  4. Save the Excel book with the file extension “.xlam”.
    Location：C:\Users\User\AppData\Roaming\Microsoft\AddIns

Add the add-in you created

  1. Click the “File” tab in the upper left of the Excel window.
  2. Click “Options”.
  3. Click “Add-ins”.
  4. From the “Manage” pull-down menu, select “Excel Add-ins” and click “Settings”.
  5. Click the “Browse” button and select the .xlam file you created.
  6. Check the add-in you added and click the OK button.

Add the add-in to Quick Access Toolbar

  1. Click the arrow on the right end of the Quick Access Toolbar.
  2. Click on “More Commands”.
  3. Under “Choose commands from” select “Macros”.
  4. Select the saved macro and click “Add”.
  5. Close with “OK.” A macro button is added to the Quick Access Toolbar.

Quick Access Toolbar