How I did it: There are a couple ways to do this!
- Look aonline at excel web resources and identify how to record/edit macros
- Buy a book on how to work with VBA in excel, my work purchsed a book like this
- Be open to learning new things about excel
- Be extremely detail oriented
Lessons & tips:
1. When recording macros, minimize key strokes as much as possible. Use the curser and not the key board arrows to navigate through the spreadsheet. Know exactly cells that you are clicking and avoid any irrelevant steps. (To many key strokes can cause errors in the macro or cause errors while running)
a. You can go into the macro code and remove extra steps but you need to be extremely careful (even erasing a space can make a difference)-just record it again! Unless you get comfortable with editing the VBA code. Editing takes less time then recording the darn things!!!!!!
b. You can’t record macros with split view, active cell positions get messed up. Be careful with using freeze pains also!
2. It is important to know that you can’t undo a macro after it is run. Therefore you have save the file before running.
3. There are a couple ways to test the macro. This is what I liked best! What learned is that macros can be run through different files, as long as file where the macro was initially recorded is open. That means you can save a macro practice file, record the macro in the actual file, and then run the macro in the practice file. Then compare the macro performance to the steps you just recorded in the original file.
4. There is a way to record macros in a personal work book. These macros are basically saved in a special file that opens simultaneously with excel. You will be able to run these macros with any file that you have open. This capability was not really useful for the consolidation file because the macros in that file have a singular use. The capability is very useful if you perform consistent tasks in multiple files. For example, if you have a data set (i.e trial balance) that you crunch with a pivot table that has all your favorite settings. You can just paste in a data set, press your pivot table macro, and your pivot table is set up and loaded just the way you like in any new work book. This is what I am playing with ;). Personal work book also useful if you created some unique reusable functions within Developer. (only allows you to use macro on your pc)5. There is also a way to share your macros, but this explanation is a little bit more complex. If we run into this question in the future, it is doable.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />