Excel – How to use Excel 2010 Personal Macro Workbook

microsoft excelmicrosoft-excel-2010vba

I want all of the macros that I've written to always be available when I'm using Microsoft Excel 2010. How can I do this?

Best Answer

In excel 2010, the personal macro workbook is located at C:\Users\UserName\AppData\Roaming\Microsoft\Excel\XLSTART\PERSONAL.xlsb. The best way to create this is to record a macro into your personal macro workbook. You do this by:

Enabling the developer tab by going to File - Options - Customize Ribbon and checking developer on the right side of the screen
Go to the developer tab and click on record macro
Store Macro In your Personal Macro Workbook
Stop Recording macro
When you close out of excel or the workbook, it will prompt you Do you want to save the changes you made to PERSONAL.xlsb select save

You will now have a personal macro workbook that is visible anytime you open excel. If you have macros stored in other workbooks you can transfer them to the personal macro workbook by moving the modules within Visual Basic in excel. Additionally, if you have a workbook with a lot of macros, you can navigate to the file location and rename that workbook to PERSONAL and save it as .xlsb

Related Solutions

Macro for cleaning all sheets in a workbook (VBA – Excel 2010)

Cleraing all sheets in a workbook:

Sub ClearAll()

    Set wbook = ActiveWorkbook

    For Each sht In wbook.Worksheets
       sht.Activate
       sht.Cells.Select
       Selection.ClearContents
       Selection.ClearFormats ' edit: clear formats too
       sht.Cells(1, 1).Select ' edit: select the first cell to cancel selection of the whole sheet
    Next sht


End Sub

Edit1: see source

Deleting them instead of cleaning:

Sub DeleteAll()

    bAlerts = Application.DisplayAlerts
    Application.DisplayAlerts = False

    Set wbook = ActiveWorkbook

    wbook.Sheets(1).Activate

    For Each sht In wbook.Worksheets
        If sht.Name = wbook.ActiveSheet.Name Then ' we don't delete the active sheet but just its conntet
            sht.Cells.Select
            Selection.ClearContents
            Selection.ClearFormats
            sht.Cells(1, 1).Select
        Else
            sht.Activate
            ActiveWindow.SelectedSheets.delete
            wbook.Sheets(1).Activate
        End If
    Next sht

    wbook.ActiveSheet.Name = "Sheet1" ' we rename the last remaining sheet to the default name

    Application.DisplayAlerts = bAlerts

End Sub

Mac – Error: The Macro May Not Be Available In This Workbook Or All Macros May Be Disabled

It looks like the button became unassigned from the macro. Try reassigning the button to the macro.

When you first right click on the button and choose Assign Macro, if you look at the "Macro name" text box, it should show you what macro is currently assigned to the button.

Best Answer

Related Solutions

Macro for cleaning all sheets in a workbook (VBA – Excel 2010)

Mac – Error: The Macro May Not Be Available In This Workbook Or All Macros May Be Disabled

Related Question