Organizing my macros

**Occamz_Razer** · 01-21-2010, 06:23 PM

Hi, I've just started writing macros to help automate my job and I've come across an annoyance. I download about 34 reports off a server for me to work on every couple of days, so I have macros that format them accordingly, run formulas, cleanup, etc. My problem is that my View Macro screen to run them has gotten very cluttered and confusing. I've tried looking for some sort of add-in that might make for a better way to organize them that's more visually appealing, but can't find anything. I also tried searching this forum as well. Am I just being picky? How do you guys that have 100's of macros organize them all? Thanks in advance for your help!

**Richard Buttrey** · 01-21-2010, 06:37 PM

Hi,

Are you sure you really need 100s of macros, or even 34? Most efficient systems make use of variables and just a handful of macros.

Do you find lots of similar lines of code in your macros? If so almost certainly you could simplify and reduce their number, and in the process make the whole system more readable.

I'd be inclined to review this first. Post a couple of typical macros if you want some further advice along these lines.

Rgds

**Occamz_Razer** · 01-22-2010, 03:13 AM

I'm definitely fairly new to this, so I'm positive I could streamline things more, I'm still trying to learn just what all I can do with VBA. My macros are completely different for each report. On one I delete columns, C,D,F, and H, trim the data, enter vlookup formulas, paste those values elsewhere, etc. On another I have it format completely differently then run an entirely different set of data. After most of my automation I have to do some work by hand, then I run a macro that finalizes the format my boss wants it in. Unfortunately, each report is different, so I've acquired many different macros for each need. Then I have other macros that do simpler tasks like take a row of data, looks for duplicates and deletes all but the first one, another macro does the same thing but deletes all but the last one. You can see how my collection can get pretty confusing

As far as making use of variables, I would love to, but don't know how yet.

**royUK** · 01-22-2010, 05:10 AM

Organise the macros into one workbook add menu and save it as an Excel addin.

**royUK** · 01-22-2010, 05:42 AM

Just added a couple of examples here

http://excel-it.com/vba_examples.htm

**Occamz_Razer** · 01-28-2010, 10:26 PM

That's awesome, thank you very much for the link, I knew there was a solution out there.

**PSB** · 04-09-2014, 11:31 PM

Hi,
I am trying to accomplish the same thing that Occamz_Razer initially requested help with. It sounds like it was solved, but links no longer work so I can not get to the examples referenced by royUK. Can someone point me in the right direction? I'm a little skeptical that the solution proposed in the earlier threads will work with Excel 2010, but I'd like to give it a try. I can't find anything else that even comes remotely close to what I'm trying to do. Thanks in advance for the help.