Macro created in All open workbooks to save in This workbook

[RAJESH1960]

Hello Everyone,
I have created a few macros in a workbook. When I open the macro in edit mode, it displays Macros in: All Workbooks. I want to save this macro in This workbook only. How is it possible to edit the macro to show This workbook always. Manually it is possible but when I close and re open the workbook again, by default it displays Macros in: All Workbooks.and every time I have to manually select This workbook.

 

[smozgur]

I assume you mean the Macros button. The macros button, that you can see the macros to edit or create a new macro manually:

... shows all macros in all workbooks as default as shown below.

However, if you enter a new macro name and click on the Create button then the sub procedure will be placed in a module in the active workbook's VBAProject.

If you want to "see" the macros resides in the active workbook, then I'm afraid you'll have to select the "This Workbook" option in the "Macros in" drop down.
However, you can still see that macros are prefixed by the workbook name in the macros list, i.e. Book2!Book2Macro - except active workbook's macros are not prefixed, Book1Macro. Unfortunately, this list doesn't show the active workbook's macros at the top but ordered by the macro name by ignoring the workbook name prefix.

If I desperately needed to do this, then I would do the following as I don't know any internal or registry settings to make it work.
Create the following macro in a module in the personal macro workbook (so it will be always loaded):

Sub CustomShowMacros()
    Application.SendKeys "%{F8} %a"
    Application.SendKeys "T {ENTER}"
End Sub

Then put this macro on the QAT: Customize Quick Access Toolbar -> More Commands -> Select Macros -> Select CustomShowMacros.

The macro will send necessary keystrokes to Excel, open the Macros dialog, and select the "This Workbook" item in the list.

 

[RAJESH1960]

I assume you mean the Macros button. The macros button, that you can see the macros to edit or create a new macro manually:

View attachment 41516

... shows all macros in all workbooks as default as shown below.

View attachment 41517

However, if you enter a new macro name and click on the Create button then the sub procedure will be placed in a module in the active workbook's VBAProject.

If you want to "see" the macros resides in the active workbook, then I'm afraid you'll have to select the "This Workbook" option in the "Macros in" drop down.
However, you can still see that macros are prefixed by the workbook name in the macros list, i.e. Book2!Book2Macro - except active workbook's macros are not prefixed, Book1Macro. Unfortunately, this list doesn't show the active workbook's macros at the top but ordered by the macro name by ignoring the workbook name prefix.

If I desperately needed to do this, then I would do the following as I don't know any internal or registry settings to make it work.
Create the following macro in a module in the personal macro workbook (so it will be always loaded):

Sub CustomShowMacros()
    Application.SendKeys "%{F8} %a"
    Application.SendKeys "T {ENTER}"
End Sub

Then put this macro on the QAT: Customize Quick Access Toolbar -> More Commands -> Select Macros -> Select CustomShowMacros.

The macro will send necessary keystrokes to Excel, open the Macros dialog, and select the "This Workbook" item in the list.

Thanks for the information smozgur.