When you create a macro in Excel, by default, the macro works only in the workbook that contains it. But if you need to use a macro in multiple workbooks what do you do?
You can use your own personal macro workbook, called PERSONAL.XLSB. This is a hidden workbook into which you can store macros for use in any open workbook on your computer.
Sounds great, but Microsoft make even this simple thing complicated. Who knows why but they do. By default the PERSONAL.XLSB doesn't exist. This isn't an issue if you are using the macro recorder to create macros, as invoking the recorder allows you to create PERSONAL.XLSB. However if you want to hand code some VBA you first have to jump through some (simple) hoops to create your very own personal macro workbook.
Here's How to Create a Personal macro Workbook
Make sure Excel is open (really! 🙂 ), you don't have to have any workbooks open, just Excel.
If you don't have it already, then follow these instructions to enable the Developer tab.
- In the Developer tab click on Record macro
- Change the Store macro in drop down to Personal Macro Workbook
- Click on Stop Recording in the Developer tab
You now have a new PERSONAL.XLSB. Sorry, the capitals are necessary, that's how Microsoft have named the file.
To make sure, press ALT+F11 in Excel which opens the VB Editor. In the Project Explorer on the left hand side of the screen, you'll see the new PERSONAL.XLSB, with the code for the macro you recorded.
If your Project Explorer isn't visible, press CTRL+R to open it.
In my case I recorded Macro1 but of course there is no actual code as I didn't do anything. I just told Excel to start recording, then stopped recording. This is enough though to achieve what we want. You can delete the macro in Module1 as we don't want it.
Press CTRL+S to save the file. Now when you close Excel and open it again, you have your own PERSONAL.XLSB file to store your most useful and often used macros.
Running The Macros
You have a few options here. You can run the macro from within the VBA editor, but more likely you will want to run the macro from within a workbook. You can press ALT+F8 to bring up your list of macros and choose the one to run.
But you can also create a shape or shortcut to run it.
However if you will be using the macros a lot, or you just like to tinker with Excel, you can create your own icons on the Ribbon or on your QAT. It's pretty easy to do, go on, give it a go.
Display Missing Dates in Excel PivotTables
Britt
Now that I have a PERSONAL.XLS (copied from MSE 2003 to 2010) workbook in my XLSTART folder, Excel no longer opens a new blank workbook on startup; it only opens PERSONAL.XLS and that’s not usually the workbook I want to work with. I can obviously open a new one every time, but I’m not used to having to do that so I forget and end up doing and saving my work in PERSONAL.XLS — not a good scenario!. Is it possible to place PERSONAL.XLS somewhere other than XLSTART and then only open it as needed? Would that give me access to the macros in it from the active workbook? Or is there another solution to this dilemma?
Catalin Bombea
Hi Britt,
For many reasons, an excel add-in is a better option than personal.xls book.
See this article for a detailed view of the problem.
You can also add a custom tab in the add-in ribbon with buttons for your macros, if needed. Here is an example.
David Terrill
Britt refers to PERSONALXLS and no start menu, I have a similar problem except I have a personal.XLSB in the usual location \Excel\XLStart. I simply would like to have the Start menu up front like it used to be before the xlsb was installed, yes it is hidden, but upon startup I get the Sheet.XLM, and no start menu. (there are a number of macros in the personal). Now I think I follow the ADDIN bit, do I need to make use of the Addin method? Can I get to have the start and the Personal.xlsb running hidden?
Catalin Bombea
The usual advice on web for hiding personal.xls is:
“When you open Excel select the Personal Macro Workbook and go to:
View >> Window >> Hide
After that, close Excel, and you will face a message asking you to save the changes you made to the Personal Macro Workbook, click Save.”
An addin is better that using the personal.xls file.
gopal dadhirao
I have a personal macro workbook.
I want this macro to do the following
on open change calculation to manual
and on close do not recalculate before save.
but when i run the macro it still does not work.
only the formatting for report date works.
thanks for the help
Gopal
with the following vba
Sub gdpersonal()
‘
‘ gdpersonal Macro
‘
‘ Keyboard Shortcut: Ctrl+j
‘
End Sub
Private Sub Workbook_Open()
Application.Calculation = xlManual
End Sub
Private Sub Workbook_BeforeSave()
Application.CalculateBeforeSave = False
End Sub
Sub HeaderDate()
ActiveSheet.PageSetup.RightHeader = Format(Date, “dd-mmm-yyyy”)
End Sub
Catalin Bombea
Hi Gopal,
Please note that HeaderDate macro works based on any sheet that is active (it’s using the ActiveSheet property)
Workbook_Open() and Workbook_BeforeSave() have codes referring to Application, not to a specific workbook, will be triggered only for the macro workbook that has these codes.
Your code will not detect other workbooks that you are opening or closing.
To setup code that can detect when other workbooks are opening, you have to create an event listener, see this article for instructions:
https://jkp-ads.com/Articles/FixLinks2UDF01.asp
Ankit
let me know if creating a new macro to the Personal workbook or deleting macros from that same workbook will prompt for Admin Credentials?
Philip Treacy
Hi,
No it shouldn’t do.
Phil
Pieter J. De Groot
degropi says:
An excel workbook that works on my PC and opens on my notebook, cannot execute my macros on the notebook,. It replies: Sorry we couldn’t find C,\Users\Work\AppData\Roaming\Microsoft\XLSTART\PERSONAL.
I want to share my excel workbooks with many third parties via my website elevatorgroupcontrols.com, however, the macros, which do not function on my notebook will also not function on the computers of third parties.
What can I do to make sure my macros will function on any computer with MS excel?.
Catalin Bombea
Hi Pieter,
You will have to include the macros in those workbooks, or you can create an add-in they have to download to make all the files work.
prashant
I need two macro which will cater to below requirement.
1. I have 20 Sheets in a work book, which has exactly same number of columns and row, only different this is, data is linked to different cities. Now I have to allow users to edit ranges, lets say $U$2:$U$200 in all 20 sheets in one go with password, and rest will be password protected
2. Reverse macro to un protect all sheets in one go
Catalin Bombea
Hi Prashant,
You have a resource here.
priya
how to share my macro file to other users. If they also need to make use of my macro.
for eg. am creating (open a picture) using macro in my ms word. how to open the same picture in other user’s pc while running the macro.?
kindly let me know asap.
Catalin Bombea
Hi Priya,
You can store your codes in a .dotm file. If you modify the ribbon to call your codes, it will be easier to use. The .dotm file can be saved in your word startup folder: C:\Users\[User name]\AppData\Roaming\Microsoft\Word\STARTUP, it will be open each time word starts.
You can find here a ribbon editor: https://www.andypope.info/vba/ribboneditor.htm
Rajiv Nair
I have a requirement for which I am unable to get such a macro working. Every day my trading platform creates an Excel file Admin Posn.xlsx with all the days transactions. I have created a macro in PERSONAL.xlsb to format Admin Posn.xlsx file deleting some columns etc then transferring it’s contents to a file called Transactions.xlsx. Now when Admin Posn.xlsx file is created I should, by using the specified shortcut key be able to run the macro stored in PERSONAL.xlsb but it does not work. In fact from the Developer tab when I access macros it is not even listed. As a workaround I use Admin Posn.xlsx file to open PERSONAL.xlsb then go back to the Admin Posn.xlsx where now the macro is listed and can be run. Since Admin Posn.xlsx is created afresh each day the initial part becomes manual. I would be grateful if a solution to this is available.
Philip Treacy
Hi Rajiv,
Where is your PERSONAL.XLSB stored? The folder location?
Can you please open a forum qs and send me a copy of your PERSONAL.XLSB and Admin Posn.xlsx.
Phil
Bere
Amazing instructions! I’m a beginner in Excel and was able to create this. Thank you! 🙂
Catalin Bombea
Thanks for feedback, glad to hear you’re happy 🙂
Cheers,
Catalin
DILSHAD AHMAD
Hi, Philip,
This video very helpful great job
Thank You,
User form in excel VBA
I need like, Site Code, Site Name, Name Service, Position, Nationality, Salary, Iqama Number, Photo is important.
Please advise how to prepare or send to me video- my email id – dilshadahmad87@gmail.com
Catalin Bombea
Hi Dilshad,
Here is an aricle about excel forms, hope this will help you start your form.
You can also try our forum, for specific problems, you will be able to upload your sample files there.
Catalin
Anikkumar
Usefull..
Klaas Vaak
I have created the PERSONAL XLSB as per your article, and it is listed in the VBA editor, as are my add-ins, which are also shown as VBAProjects.
I have a macro (automatically update pivot tables when saving the file/workbook) that I have installed in 3 workbooks, but then realised I would be handier to have the macro in the PERSONAL XLSB, esp. if I create more pivot tables in other workbooks. So here are my questions:
1. is it not simpler to create an add-in, which avoids fiddling around with the VBA editor
2. if putting it in the PERSONAL XLSB is better, how do I do that? Right now there is 1 macro in there in the folder Modules. Should I create a new Module for the new macro?
3. My objective is that the new macro is active for each workbook I open, i.e. when, after making changes to an open workbook, I save it the pivot tables in it are updated automatically. From your article above I get the impression you have to run the macros in PERSONAL XLSB manually or with a shortcut. If that is true I don’t find it user friendly & prefer add-ins.
4. The macro I currently have in the PERSONAL XLSB is to have Excel open maximised. In practice, however, Excel sometimes opens maximised, sometimes it does not, which makes me wonder if the macro is actually activated when I open Excel. If it is not activated it means I am doing something wrong, or my understanding of PERSONAL XLSB is wrong.
I hope you’ll find time to help me out.
Many thanks in advance.
Catalin Bombea
Hi,
You will find answers to your question in this article.
An Add-in is a better option.
Catalin
Klaas Vaak
OK Catalin, thanks a lot for your help 🙂
Burnie Huddleston
Does the .XLSB work on any file? I just started teaching myself macros because I need to stream line some things for my department. I created a test file that I played with to get the macros and everything set up. It works perfect on the file, but when I try to use it on a different file it bugs out. I also need to share the .XLSB with the other guys in the department so they can use it as well. The error shows to be in a line:
ActiveWorkbook.Worksheets(“valve test”).AutoFilter.Sort.SortFields.Clear
I’m thinking that is directing it to the original file I used, making it file specific. I created another macro with a different file and the new name replaced (“valve test”) as the default with the same results.Hopefully you can help me out. Thank you.
Philip Treacy
Hi Burnie,
Yes the .xlsb will work with any file, except when you hardcode references to sheets and workbooks which other people won’t have access to. Which may be the case when you distribute the .xlsb to your colleagues.
If you could open a Helpdesk ticket and send me your .xlsb and the file(s) you are working with, I’ll see what I can do.
Cheers
Phil
Burnie Huddleston
Thanks for the help. I played with it and was able to figure it out, it just took a while.
Philip Treacy
OK, thanks for letting us know you got it worked out.
Phil
Terri Booker
I am using 2010 excel, Windows 7… I have tried everything – but with no success – I keep getting the error “Personal Macro Workbook in the startup folder must stay open for recording” – I don’t show disabled, however, I don’t show in my XLStart Folder either, and I can’t find one when I do a file search… I know there is a way to do this, but I am at a loss.. Can I manually create one, and save into my XLStart folder, if so, what do I need to do in the Properties? Hope you can help…
Philip Treacy
Hi Terri,
Are you getting this error when you are trying to create the Personal.xlsb or after you have done so and are trying to create other macros?
When you say you ‘don’t show disabled’, do you mean that you’ve gone into File->Options->Add-ins->Disabled Items and checked if Personal.xlsb is disabled?
Also, when you say ‘I don’t show in my XLStart Folder either’ do you mean you can’t see Personal.xlsb in that folder?
After you open Excel, go into the VBA editor (ALT+F11), do you see Personal.xlsb in the Project explorer? Press CTRL+R if your Project Explorer pane isn’t open. I’m wondering if Personal.xslb exists but you can’t see it in the folder because it is hidden?
After trying to create the Personal.xlsb, then closing and reopening Excel, if you go to the View tab and click on Unhide in the Windows group, is Personal.xlsb listed?
Do you have write access to the XLSTART folder? Can you create a new file in there?
You can try creating a Personal.xlsb on another computer and then copy that to your XLSTART folder located at C:\Users\\AppData\Roaming\Microsoft\Excel\XLSTART
Regards
Phil
Terri Booker
Thanks for all your help, and I am now good to go… I am rockin and rollin now with macros, and so on thanks to you – but I’ve run into something I cannot seem to resolve or figure out… I don’t know if you have an answer for me or not…
I export csv report files, w/multiple HTML code tags – I have no problem in converting HTML “” code i.e.:
“br” w/Replacement:= Chr(13) & Chr(10),
“ol” w/Replacement:=””
“li” w/Replacement:=Chr(149)
However, what I really need to be able to do using VBA is replace the “li” to a number sequence always starting with 1. then+1 i.e.: 1. 2. 3. 4. etc, and be able to restart the number sequence in each cell where needed – starting with 1…
I am sure this is possible, but I have had a bad case of “Stuck on Stupid” and I can not figure out how to do this. I can have anywhere from 1 row of cells to 50 rows always with 2 columns A & B with “li” html Code. It’s only the “li” HTML replacement I am having an issue with that has me stumped… thanks in advace… tbooker
Philip Treacy
Hi Terri,
You’re welcome.
Can you provide a sample CSV and the code you use for this so I can take a look? If you open a Helpdesk ticket you can attach files to that.
Cheers
Phil
marloski
hi philip,
I made this personal workbook and save my macros in it and shortcuts. However, I have this macro to split the sheets/tabs of another workbook. All other macros are working on all open workbooks.
My code from the internet:
Split a workbook into multiple workbooks and save in the same folder.
Sub Splitbook()
‘Updateby20140612
Dim xPath As String
xPath = Application.ActiveWorkbook.Path
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each xWs In ThisWorkbook.Sheets
xWs.Copy
Application.ActiveWorkbook.SaveAs Filename:=xPath & “\” & xWs.Name & “.xlsx”
Application.ActiveWorkbook.Close False
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
what I’d done is open the workbook and paste this code on VBE again and again, instead of just opening my personal workbook. thanks in advance.
Catalin Bombea
Hi,
Please take a closer look at this line from your code:
For Each xWs In ThisWorkbook.SheetsThisWorkbook refers to the personal workbook, you should change this to point to the active workbook instead.
Cheers,
Catalin
Anon
Thank you! How do you disable the “read only” for personal workbook when you open a second instance of excel, say on second monitor? I’ve searched all over for an answer to this but I cannot seem to find the answer. (FYI I know about hiding/unhiding the personal workbook)
Philip Treacy
You’re welcome 🙂
If you already have Excel open you can’t disable the ‘PERSONAL.XLSB is locked for editing’ warning when you start another instance.
When you start another copy of Excel, you are trying to open the PERSONAL.XLSB again. The only options available to Excel are to open a read only copy, open a read only copy and notify you when the file is no longer locked, or not open PERSONAL.XLSB at all. If you do click Cancel at this warning, Excel will still open.
Phil
MF
Hi Philip,
Thanks for the article.
I have encountered a strange situation that my PERSONAL macro doesn’t open with Excel. I am sure it is saved in C:\…Excel\XLSTART
I’ve tried many different ways including “At startup, open all files in” the above path. It doesn’t help.
My workaround is to pin the PERSONAL.XLSB in the recent document and open it manually when I need my personal macro.
Any idea?
Cheers,
MF
Philip Treacy
Hi MF,
If you open the VB editor (ALT+F11) it’s not listed in the Project Explorer?
I guess it could be corrupted. You can copy out all the code you want from it, then delete it from your XLStart folder, recreate a new one, then copy the code back into it.
Phil
MF
Hi Philip,
Thanks for your prompt response.
I have just tried your suggestion. It doesn’t help. 🙁
Forgot to mention: I have Excel 2002 installed and the personal.xlsb opens with it. Nevertheless, both version Excel 2002 and 2010 coexists for a while. They both opened personal.xlsb automatically in the past, but in one day Excel 2010 doesn’t anymore. I have no idea what I have done…
Cheers,
MF
Philip Treacy
Hi MF,
Why are you using ver 2002 and 2010 at the same time?
Anyway, your personal.xlsb could be disabled. In Excel 2010, go to File -> Options -> Add-ins.
At the bottom of that window, in the ‘Manage’ drop-down list, choose ‘Disabled Items’, then click on Go.
Is your personal.xlsb listed there as disabled? If so, re-enable it.
If it has been disabled it may have caused some issues that Excel didn’t like and disabled it. This may happen again, and if it does, I’d go through the steps above to create a new personal.xlsb.
Cheers
Phil
MF
Hi Philip,
Thanks so much for your tips. It helps! You are my star!
I have to keep Excel 2002 because there are many branches in my company are still using them… and I need to create some template for them from time to time. I need Excel 2002 to test and to ensure what I created is “compatible” to them.
Cheers,
MF
Philip Treacy
🙂 glad to help.
Yes I understand re: 2002. We have 07, 10 and 13 installed, and I’ve got a copy of Office XP and Office 2000 around somewhere too.
Cheers
Phil