Combine multiple macros to be as one?

[MAMC84]

Hello,

I have recorded several macros and would like to figure out if I can string them together to execute in the proper order, and to behave as one macro. My goal is to achieve this, and then create a button that will execute this new all-encompassing macro. Is this possible? Can anyone explain to me how I can string these multiple macros so that it becomes a single macro?

I have no VBA skills, so any simplified help would be appreciated.

Thanks!

[royUK]

You can create another macro to call the existing ones.

Sub main()
    Call Macro1
    Call Macro2
    Call Macro3
    Call Macro4
End Sub

[hutch@edge.net]

I'm still a novice, but this is my first chance to help anybody, and I've received so much help from the gurus on this site that I want to do my part. Here is how I combine macros.

Open the workbook containing the macros. Go to "Tools", "Macro", and select "Visual Basic Editor". Expand the "Modules" folder, and you will see all of the modules in the open workbook. You can view what macros are in each module by double clicking on the module.

Right-click on "Modules", and "Insert" a new module. This is where you will build your combined macro, without disturbing the macros you've already created.

Now, name your new sub. At the top of your new module, type:

Sub Your_Name_Here()

Be sure NOT to use any spaces in the name. If you want to split it up, use the underline _.

Now, build your new macro by simply double clicking the modules containing your existing macros, and copy and paste them in the order you want them to run. I would initially paste the whole macro, with the name and End Sub still in it, and go back and clean it up later. This keeps them seperated and easier to understand.

Next, to combine them, you just have to select and delete the "Sub" and "End Sub" statements, (with the exception of the ones at the beginning and end of your macro), and the new macro will be combined.

You will probably want to clean it up a little more, removing the clutter like the extra empty lines and the apostrophe statements. (Lines that start with ' don't execute anything, they are descriptive lines for the programmer). Be careful to keep your indents the same, as I'm pretty sure this is important.

When done, just close the editor, and assign the new macro to whatever button you want. Run it and see if it does what you want it to. If not, go back and check you new macro in the editor, and see if you can figure out the problem. If it works the way you want, go back in and delete all the modules containing all the old macros, but be sure it's working right before you do this. Be sure to save the workbook before you exit.

I hope this helps you get started. Some of the more experienced users on this sight may have better suggestions, but that's the way I've been doing it.

Hvae a good one.

[hutch@edge.net]

See what I mean about a more experienced user having a better suggestion? Roy answered your question a whole lot better while I was spending all that time typing to tell you the wrong way to do it.

I guess I'm not quite ready to start trying to help yet.

Anyway, I think I'd go with Roy's suggestion, as he's never steered me wrong.

Jerry

[royUK]

See what I mean about a more experienced user having a better suggestion? Roy answered your question a whole lot better while I was spending all that time typing to tell you the wrong way to do it.

I guess I'm not quite ready to start trying to help yet.

Anyway, I think I'd go with Roy's suggestion, as he's never steered me wrong.

Jerry

There's nothing wrong with your suggestion.

What should be remembered is that recorded code can usually be edited to be more efficient.

[MAMC84]

Thanks to both of you. I wish I knew how to write these macros, particularly as these recorded macros always seem to have errors! Thanks again.

[desware]

Hi Guys,

I am new to the forum so hello out there! Just a quick question regarding combining seperate macros. I have tried this with Excel 2007 using Roys technique (Call code) however cannot understand how to save it! the way i am currently doing it is:

1. Create a dummy macro to give me the option of editing it
2. Once created i open up the macro box and select the dummy macro and edit
3. I then place in roys code and rename the macro's accordingly
4. I am then clueless as what to do. I click the save disk button in the top left hand corner? I have also tried to press the play button but keep getting errors.

Any help would be greatly appreciated.

[royUK]

desware welcome to the Forum, please start your own thread with a link to this one. This is covered in the Forum Rules