using a variable workbook's name to define another variable

In the workbook that is active when the macro starts running, the user has selected two things:

1. The name of the workbook they want to run macros on, and
2. The name of the macro they want to run

the variable StepOne needs to be defined as the workbook name that is listed in range B6 of this workbook.
the variable StepTwo needs to be defined as the macro which is named in range B9 of this workbook.


The thing is, this entire process works just fine if I manually F8 through the macro and make sure I manually select the right workbook for Excel to read out of before this line hits. I have the code set to Set StepOne = Sheets("Start").Range("B6").Value, which works because I'm manually selecting the correct workbook ahead of time.

This is why I'm trying to define the workbook (as DWMBook) so that I can use that as a reference when defining these two variables above.

So the final result is:

StepOne.Activate 'activates the workbook that the user chose
and
Application.Run StepTwo 'runs the named macro on the active work book, which is StepOne.

Does this make sense?

Originally Posted by Norie

Sorry I'm a bit confused.

If the user is naming the workbook in B6 and the macro named in B9 is in that workbook wouldn't it be more like this.

Please Login or Register  to view this content.

By the way, this will always error, StepOne is declared as a Worksheet Object so you can't set it to refer to a value from a cell.

Please Login or Register  to view this content.

Sorry I've confused you Maybe an image will help?

ef.png

From my post above:

The thing is, this entire process works just fine if I manually F8 through the macro and make sure I manually select the right workbook for Excel to read out of before this line hits. I have the code set to Set StepOne = Sheets("Start").Range("B6").Value, which works because I'm manually selecting the correct workbook ahead of time.

I need to define StepOne as a workbook - not a string, because if I define it as a string, I can't possibly execute this code:

Please Login or Register  to view this content.

The value of StepOne is an open workbook. I need to activate that workbook, THEN run StepTwo on that workbook.

My sole issue here is that I don't know how to write the syntax to embed the variable DWMBook into the following lines:

Please Login or Register  to view this content.

The only way so far I can get this to work is by doing the following:

Please Login or Register  to view this content.

This makes DWMbook active, then executes the code on that. Since it's the active workbook, I don't have to define anything. But it's not always optimal for me to do it this way. Instead of doing this:

Please Login or Register  to view this content.

I'd like to do this:

Please Login or Register  to view this content.