Now that we've added some useful functionality to PERSONAL.XLSB, it would be nice to be able to back it up and move it from machine to machine. This is a relatively simple task since PERSONAL.XLSB is just a workbook stored in binary format.
The reasons behind moving PERSONAL.XLSB are simple: we've made macros that make our work easier and it would be nice to be able to transport those macros either to a new machine or to a machine at home or at the office so we can always use our macros. We will be taking a look at where PERSONAL.XLSB is stored, and we will save it to the Desktop so we can move it elsewhere from there.
Using PERSONAL.XLSB on Other Machines
There are a few things that should be considered when transporting your copy of PERSONAL.XLSB from one machine to another. The main reasons we would want to transport our PERSONAL.XLSB to another machine are if we were to purchase a new machine and wish to use our Personal Macro Workbook on the new machine and if we use one machine at work and another machine at home and would like all of our macros to be in both places.
The following list explains some things to consider when moving PERSONAL.XLSB and how to work around them:
- Permanent vs. Temporary Use—Are the macros in PERSONAL.XLSB intended to be permanent additions to the new environment? If the macros are intended to only be used for a short period of time, it is easier to just open the workbook you're moving and run macros from there. If they are intended to be permanent additions, PERSONAL.XLSB should be moved into the XLSTART directory on the new machine.
- Overwriting an already existing PERSONAL.XLSB—Does the machine you are moving PERSONAL.XLSB to already have and use PERSONAL.XLSB? If so, it would be best to open the machine's current PERSONAL.XLSB and the one you are moving and copy the macro code from the new workbook to the old workbook. This way the old macros are preserved while adding the functionality of the new macros.
- Macros with the same names—If you are copying and pasting the code from one PERSONAL.XLSB to another, the macros being moved need to have names that are unique. If an old macro and a new macro share a name, both macros become non-functional since Excel can't tell which macro to run. A simple change like adding a prefix or suffix to the new macro names (i.e. AutoFit-n vs. AutoFit) will solve this problem.
- Keyboard shortcuts—Because of the way keyboard shortcuts are associated with macros, any keyboard shortcuts associated with copied and pasted macros may no longer work. If this is the case, the macro needs to be added to the Quick Access Toolbar, or it needs to be run from the Macro menu.
Most of these issues can only be resolved by editing code in the Visual Basic Editor; however, the modifications are simple and shouldn't cause too much of a headache.
Saving PERSONAL.XLSB to the Desktop
While we have PERSONAL.XLSB open, we will save it to the Desktop.
Step1.To save PERSONAL.XLSB to the Desktop,
Click FILE, Click Save As
Step2.To change the file's location, in the left Favorite Links bar,
Click Desktop
Step3.To name the file, in the File name field, type:
PERSONAL
Step4.To change the file type, in the Save as type drop-down list,
Click
, Click Excel Binary Workbook (*.xlsb)
Step5.To finish saving the file,
Click
Step6.To hide the Personal Macro Workbook, on the Ribbon,
Click
NOTE for MacOS Users: To hide the Personal Macro Workbook
Click WINDOW, Click Hide
Quitting Excel
Let's go ahead and close Excel.
Step1.To close Excel,
Click
NOTE: You may need to choose Exit Excel more than once. Sometimes when you use the Personal Macro Workbook, Excel will stay open after the first time you tell it to close. Telling the application to close again will make it quit.
Step2.If you are prompted to save the Personal Macro Workbook,
Click
NOTE: To find more training and information about Excel, go to: