Exporting all Formulas and Code used in a Workbook

Short of making a copy of the workbook, you're not going to be able to get everything.

However, at least for code in "regular" modules, you can save the modules to BAS Files. The BAS files are text files of the module with a bit extra information included such as the module name and keyboard shortcuts.

First, give you module a meaningful name. Personally, I keep the "mod" prefix although I'm not sure why. But I have modules like ModMailer and ModClearTable. I generally have the code in subroutines that can accept arguments passed to them so all I have to do is import the module and call it from my main code as opposed to modifying the module itself. Also I will include "like" functions in the same Module. For example, my ModClearTable module has two main subroutines: ClearTable which calls ClearFilter so both subroutines are available in the same package.

Select the module and then File -> Export. Navigate to where you want to save the modules. I have a Folder Called MyExcel in my home directory. Excel "remembers" the last accessed folder so it's usually pointing to this directory when I export or import a module. To import a module, open the VB editor and select File -> Import.

This technique only works with "regular" modules and Excel forms. Code embedded on sheets don't work this way. The only way I can think of doing that is to make a "template" out of the workbook with the data removed. You could, in code, open the template and copy the sheet to the target workbook.

As for formulas I have a "cheat sheet." For example, if I need to find the last occurrence of a value in the range, I look at the sheet posted on the wall next to my computer: =MAX((Range=Value)*(ROW(Range)) - I have to "know" that this is an array formula.