Tag Archives: Microsoft Excel

The Holy Grail of Excel Macros

Okay, so, you just got handed a job for a 100+ page workbook. Your client wants you to fix some formatting or something of the like on every page (the 100 pages already exist… creating that many is a different story…). What do you do? You use macros and let the computer do all the work, silly! 😛 The best part is you can charge for all the hours it would have taken you to fix it all up without the snippet I’m about to give you! (Okay, this is probably a tad unethical…).

You’re interested? I thought so. We’re going to use the power of foreach loops to let the computer run through and do all this on every single page. The workflow I use looks something like this:

  1. On the first page (or a “test” page), I hit Record Macro and name it something I can remember. Be sure that you remember what the exact name of it is. This is very important. 
  2. Proceed to style/do whatever it is that you’re being paid to do. Ahem. 
  3. Once you’re done, stop recording the macro. At this point, I usually like to test the macro before I use it on my entire workbook, just to make sure it isn’t going to destroy it all. 😛
  4. If you have verified the integrity, now we need to write a bit of code. :O But I have never used the Visual Basic editor! Don’t worry–it’s incredibly simple and you can basically copy and paste this snippet I’m about to give you and just change one little bit for all your repeating macros. 

Let’s get on to the code now, shall we?

Continue reading