Use one macro across workbooks with PERSONAL.XLSB

Create your Personal Macro Workbook

1. Open Developer → Record Macro. Enable the Developer tab in ribbon settings if needed.
2. Choose Personal Macro Workbook in Store macro in. Give the macro a name and start recording.
3. Select a cell, then stop recording.
4. Quit Excel and save changes to the Personal Macro Workbook when prompted.

Excel normally stores this as PERSONAL.XLSB and opens it hidden on the next launch. It is local to the computer, not automatically shared with every recipient of your spreadsheets.

Move an existing macro

Press Alt + F11 and copy the required code into a standard module under VBAProject (PERSONAL.XLSB). Close the original workbook and test against a disposable sample.

Hard-coded workbook names, sheet names, and paths still point to their original targets. Moving code does not automatically make those references reusable.

Understand ThisWorkbook and ActiveWorkbook

ThisWorkbook means the file containing the code: PERSONAL.XLSB in this case. ActiveWorkbook means the workbook currently active in Excel. Check the target before acting on it.

Public Sub ShowTargetWorkbook()
    If ActiveWorkbook Is Nothing Then Exit Sub
    If ActiveWorkbook Is ThisWorkbook Then Exit Sub
    MsgBox ActiveWorkbook.Name
End Sub

This demonstration only displays a workbook name. For editing macros, capture the intended workbook in a variable and qualify every sheet and range through it.

Troubleshoot and back up

1. Confirm that you saved PERSONAL.XLSB when closing Excel.
2. Check that its VBA project is open after restarting.
3. Respect your organization’s macro policy. Do not enable every macro just to get a file working.
4. Back up the file before moving computers. Replacing another PERSONAL.XLSB can overwrite macros already stored there.