It will only save to the file that it is in.
If you want it to be available for other workbooks setup the macro in a blank sheet and save it as a .xlam file in C:\Users\(Your User Name)\AppData\Roaming\Microsoft\AddIns\
Then in Excel Options under Addins, select "Go" and find the Addin that you have created and ensure it is ticked and then it should be available every time you open Excel.