Variable values from one macro to another

**koltregaskes** · 08-24-2011, 05:26 AM

If I set a variable in one macro willl it still be set when I run another macro? What is happening is this:

1. I receive one or more trackers from different agents

2. These are placed in a central folder untouched

3. I run my Excel macro. The starting macro is simply a a list of other macros it will run

4. the list of macros are for each possible tracker I receive from our agents. If it finds one in the central folder it will do a bit of formatting then rename and copy the tracker to certain locations on the network and intranet. This all works fine so far.

5. At this point I have a variable statement such as Agent1Tracker = Yes (baically to saythat yep the macro has found and updated that paritcular agent tracker)

6. Then the theory is that the last sub macro will run Access. In Access there are macros assoicated to each agent tracker (to import their data into our db). What I'd like is that this only runs the Access macros that have a "Yes" variable and ignoring any trackers not found (just to be more efficient). So the statement is if, for example, Agent1Tracker = Yes then run Agent1Tracker Access macro

This doesn't seem to work. Perhaps I'm not setitng the variable correctly but I've tried Agent1Tracker = Yes and it runs ALL Access macros. I've tried using Agent1Tracker = "Yes" (in speech marks) and it runs none of them.

It's not a huge issue but if I only get one of our agent's trackers then running all the Access macros is a lot of wasted time.

Basically then, if I set AgentxTracker to Yes in the first macros will it be remembered when it comes to the Access macro?

The workaround it to just run all Access macros but I'd like to avoid this.

Any ideas? I hope I've explained it well. If not please let me know?

**koltregaskes** · 08-24-2011, 05:32 AM

In answering my own question, I suppose what I could do if the variable value doesn't get remembered from macro to macro is to re-org all the macros:

1. Set a check for each agent tracker in the starting macro

2. If it finds a tracker then run the sub tracker

2. Set the variable there when it returns to the starting macro

3. Run the Access macros from the starting macro where the variables = Yes

Is this what I need to do?

**koltregaskes** · 08-30-2011, 08:07 AM

I've implemented my own suggestion of moving the variable statements all into the main macro but it still doesn't work. Any ideas? Is it simply because the variable is forgotten when you run another macro.