Auto-Scroll All Sheets to Cell A1 with a VBA Macro

Open a workbook and every sheet is scrolled to some random cell from the last time someone used it? This macro fixes that. Run it once and every sheet resets to A1 — no scrolling, no hunting.

What this macro does

Loops through every sheet in the active workbook, selects cell A1, and resets the scroll position to the top-left. It stores your current sheet, does the work, then returns you to where you started. You save it once to your Personal Macro Workbook so it's available in any Excel file you open.

Step-by-stepMac

Enable the Developer tab

Go to Excel → Preferences → Ribbon & Toolbar. Under "Customize the Ribbon," check the box next to Developer. Click Save. You should now see a Developer tab in your ribbon.

Create your Personal Macro Workbook (first time only)

Click Developer → Record Macro. Set "Store macro in" to Personal Macro Workbook. Give it any name (e.g. "setup") and click OK. Then immediately click Stop Recording. This creates a hidden file called Personal.xlsb that stores macros available in every workbook.

Already have a Personal Macro Workbook? Skip to step 3.

Open the VBA Editor

Click Developer → Visual Basic, or press Option + F11.

Find your Personal.xlsb

In the Project Explorer panel on the left, expand VBAProject (PERSONAL.XLSB) → Modules → double-click Module1 (or any module listed there).

Don't see a Project panel? Go to View → Project Explorer.

Paste the macro

Delete any existing code in the module (the "setup" stub from step 2). Then paste the macro code from the panel below.

Save and close

Press Cmd + S. Excel saves the Personal Macro Workbook automatically — you don't need to do anything special.

Run it in any workbook

Open any workbook with multiple sheets. Click Developer → Macros → select PERSONAL.XLSB!SetAllSheetsToA1 → click Run. Every sheet resets to A1 and you land on the first sheet.

Tip: go to Developer → Macros → Options to assign a keyboard shortcut like Cmd + Shift + A.

VBA Macro

SetAllSheetsToA1

Sub SetAllSheetsToA1()
    Dim ws As Worksheet

    For Each ws In ActiveWorkbook.Worksheets
        ws.Activate
        ws.Range("A1").Select
        ActiveWindow.ScrollRow = 1
        ActiveWindow.ScrollColumn = 1
    Next ws

    ActiveWorkbook.Sheets(1).Activate
End Sub

Paste this into Module1 inside VBAProject (PERSONAL.XLSB) in the VBA editor.

Doesn't work?

Try this prompt in Claude, ChatGPT, or Gemini

Copy and paste this — it'll walk you through exactly what's different about your setup and how to fix it.

“How do I create a Personal Macro Workbook in Excel on a Mac and add a reusable macro to it? I want to store a macro that scrolls all sheets in any workbook to cell A1 so I can run it whenever I need it. Please walk me through each step, including how to enable the Developer tab and where to paste the code in the VBA editor.”

ClaudeChatGPTGemini

Back to Excel Tips